Excel Tips
Tables Are Like a Database in Excel »
May 30, 2022
Excel isn’t like Access. I am an Access person and Excel annoys me.
Whatever Happened to the @@ Function? »
May 27, 2022
Back in Lotus 1-2-3, there was an @@ function. If you used @@(A3), Lotus would go to A3. A3 was supposed to contain a valid cell reference. Say that A3 contained the text C5. The @@ function would then return the value from cell C5.
How Can You Test for Volatility? »
May 26, 2022
How do you know OFFSET is volatile and INDEX is not?
Replace Volatile OFFSET with INDEX »
May 25, 2022
OFFSET is a volatile function. It breaks the smart recalc feature of Excel. A single OFFSET function near the top of the worksheet will cause every formula in the calculation chain to recalc constantly.
SUM a Range that is C5 Rows Tall Using OFFSET »
May 24, 2022
I need to add up a range that starts in cell A5 and is C5 rows tall. A formula in C5 is calculating a number and I need to include that many cells in the SUM.
Switching Columns into Rows Using a Formula »
May 23, 2022
Every day, I receive a file with information going down the rows. I need to use formulas to pull this information into a horizontal table. It is not practical for me to use Paste Special, Transpose every day. Below, you can see that the first formula in B2 points to A4. If I drag this formula to the right, there is no way that it will pull values from A5, A6, A7, and so on.
Build a Model to Predict Sales Based on Multiple Regression »
May 20, 2022
I run a Gelato stand. After 10 days of sales, I discovered that each day, I would either make a lot of money or nearly go broke. As I analyzed sales, I began to feel that temperature and rain might be two important determining factors in how much money I make. On rainy or cool days, fewer people buy gelato.
Forecast Data with Seasonality »
May 19, 2022
Straight-line forecasting won’t work because our sales are seasonal. We sell more near the Christmas holiday and less in the summer.
Calculate a Trendline Forecast »
May 18, 2022
I have monthly historical sales data. I want to predict future sales by month.