Solutions for Data Science

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...

Ctrl+1  to Format a Selection Ctrl+1  (the number one) works to format whatever is selected. Whether it is a cell, SmartArt, a picture, a shape, or the March data point in a column chart, press  Ctrl+1 . Ctrl[+Shift]+Arrow  to Navigate or Select Your cell pointer is sitting at the top of 50K rows of data, and you need to get to the bottom. If you have a column with no blanks, press  Ctrl+Down Arrow  to jump to the end of the data set. In the following figure,  Ctrl+Down Arrow  will jump to K545.  Ctrl+Left Arrow  will jump to A1.  Ctrl+Right Arrow  will jump the gap of empty cells and land on N1. Add the  Shift  key in order to select from the active cell to the landing cell. Starting from A1 in the above figure, press  Ctrl+Shift+Down Arrow  to select A1:A545. While still holding down  Ctrl+Shift , press the  Right Arrow  Key to select A1:K545. If it seems awkward at first, try it for a few d...