What's Wrong With These Formulas????

[scotfitz]

I have a spreadsheet that I use as a template.

I fill in 12 months worth of data, clear all of the contents
and then reuse the same template for the next twelve months.

When I cleared the contents recently I got an error message on
the two formulas posted below. There is a little green corner in
the upper right hand corner and the information baloon says that
"Formula omits cells in region."

The formulas appear correct. They are.....

In field F10 is =COUNT(F4:F9)

In field F11 is =IF(COUNT(F4:F9),AVERAGE(F4:F9),0)

The formula was working fine until I cleared all contents.

Help is always appreciated.

[Paul]

Hi Scotfitz,

What you're seeing is not so much an "error" as it is a thoughtful warning provided by Excel. It's simply saying that the formula you're using might be excluding similar cells in the current region (e.g. cells with numbers above/below the range specified in the formula).

From Microsoft.com:

The formula may not include a correct reference. If a formula refers to a range of cells, and you add cells to the bottom or right of that range, the references may no longer be correct. The formula does not always automatically update its reference to include the new cells. This rule compares the reference in a formula against adjacent cells. If the adjacent cells contain more numbers (are not blank cells), then the problem is noted.

For example, the formula =SUM(A2:A4) would be noted with this rule, because A5, A6, and A7 are adjacent, and contain data.

To turn off this flag, go to Tools -> Options -> Error Checking tab -> un-check "Formula omits cells in region". Click OK.

You'll also see other "errors" that Excel tries to help you notice.