Excel Tips
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.
Generate Random Without Repeats »
May 17, 2022
I want Excel to generate numbers for the lottery. Once a number is chosen, I don’t want that number to appear again. Using RANDBETWEEN, it is possible to get duplicates.
May 16, 2022
My Monopoly set is missing the dice. How can I create a spreadsheet that will simulate randomly rolling two dice?