Median Values Not Calculating

Some of the median calculations in workbooks require what are called “array formulas.” Formulas can sometimes lose their status as array formulas and switch back to normal formulas, generally if a worksheet is copied from one workbook to another. This will cause the median value to cease to calculate. There are two places where this can occur, and it’s easily fixed on both.

To fix an array formula:

1. Click the cell containing the formula.
2. Click the formula bar near the top of the window (you may want to click to the right of the formula text), hold down the Shift and Control keys, and hit Enter.

You will notice the formula in the formula bar near the top of the Excel window is surrounded by curly braces after doing this, and the calculation should resume working.

Example:

Before –

After –

Here are the two places median calculation issues can occur, and how to fix them. 

On the Expense Grid Worksheet

Median calculations are found on the upper part of column BM. You may want to hit the Toggle Display button on the left to make sure all of them are visible. (If clicking it hides rows instead of showing them, just click it again.)

1. Starting with the top cell under the “Med.” heading, click the cell.
2. Next, click the formula, hit Ctrl+Shift+Enter, hit the Down key to go to the next cell, and repeat.

With this Click->Ctrl+Shift+Enter->Down cycle, it can be corrected in the span of about thirty seconds.

On the Land/Sales/Lease Grid Worksheet

There are two formulas that calculate the median value in the “Ranges & Reconciled Value” table below the main analysis grid. These are hidden (via the text being colored white) immediately under the unadjusted median cell. 

Just click on each cell, click the formula bar at the top, and hit Ctrl+Shift+Enter to restore them.