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.

Play Dice Games with Excel »

May 16, 2022

My Monopoly set is missing the dice. How can I create a spreadsheet that will simulate randomly rolling two dice?