Solutions for Data Science

CAGR stands for  C ompound  A nnual  G rowth  R ate.  It describes the rate at which an investment would have grown over several years if it had grown at the same rate every year on a compounding, rather than simple, basis.   The CAGR metric is calculated using the following formula: If we were to fit this entire formula into a single measure it may get messy and confusing for other users, so let’s step it out. We will need to create several measures to calculate the individual pieces the CAGR formula.  Of course, this is not the only way to calculate CAGR in Power Pivot, but this is the way we’ve decided to go about it.  So, let’s break it down, we will need the following measures to calculate CAGR: A measure to retrieve the First Year in the data set A measure to retrieve the Last Year in the data set A measure to calculate the Number of Years between the First and Last Years in the data set A measure to aggregate the total sales in the data set...

You have 12 identical worksheets, one for each month. You would like to summarize each worksheet. Is there a better way than using =Jan ! B4 + Feb! B4+Mar! B4+Apr! B4…? Solution:  You can use a 3-D formula such as  =SUM(Jan:Dec!B4) , as shown in  Figure 41 . Figure 41. A 3-D formula adds up all instances of B4 on the 12 sheets from Jan through Dec. Late-breaking Tip : To add up cell B4 on all the worksheets with Sales in the sheet name, type  =SUM(‘*Sales’!B4)  and press Enter. If the first or last worksheet contains a space in the name, you have to use apostrophes around the pair of worksheet names:  =SUM(‘Jan 2009: Dec 2009’!B4) . You can easily copy this formula to other cells on the summary worksheet.  Gotcha:  Do not drag the summary worksheet to appear after the Jan worksheet, or you will set up a circular reference. Additional Details:  It is possible to set up a named range that refers to a 3-D range. Here is an interesting way to set...

INDIRECT  is pretty cool for grabbing a value from a cell. Can  INDIRECT  point to a multi-cell range and be used in a  VLOOKUP  or  SUMIF  function?  You can build an  INDIRECT  function that points to a range. The range might be used as the lookup table in a  VLOOKUP  or as a range in  SUMIF  or  COUNTIF . In  Figure , the formula pulls data from the worksheets specified in row 4. The second argument in the  SUMIF  function looks for records that match a certain date from column A. Note:  Because each worksheet might have a different number of records, I chose to have each range extend to 300. This is a number that is sufficiently larger than the number of transactions on any sheet. The formula in cell B5 is: =SUMIF(INDIRECT(B$4&"!A2:A300"), $A5, INDIRECT(B$4&"!C2:C300")) Summary:  You can use  INDIRECT  to grab data from a multi-cell range.

We begin with a list of stock ticker symbols and their respective company names. Our objective is to display the monthly close stock price from a user-defined date to the present.  We also want to display an in-cell line chart that visualizes the monthly price changes while identifying the monthly high and low over the requested time. The  STOCKHISTORY  function retrieves historical data about a financial instrument and loads it as an array, which will spill if it’s the final result of a formula. This means that Excel will dynamically create the appropriately sized array range when you press  ENTER . The syntax for the STOCKHISTORY function is as follows ( optional arguments are in square brackets ): =STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5]) stock  – Enter a ticker symbol in double quotes ( g.,  “MSFT” ) or a reference to a cell containing the Stocks data...