Solutions for Data Science

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