CODEWITHSUNDEEP
excelexcel-formulaaverage
ConorCK
ConorCK

Reputation: 11

Average Formula error

I am trying to work with an average formula, that includes a few other average formulas within.

The results of my formula are either a numerical value, or 'FALSE'.

When I highlight the 8 values I get the correct average calculationin the example below result is -2.5. and when I use =AVERAGE(A2,A3,A4......A9) I get correct result as -2.5.

However, when I replace A2 within the Average formula with the formula within cell A2, I get a different result.

[Image trying to explain the issue][1].

appreciate any help on this

Upvotes: 1

Views: 94

Answers (1)

M.Douda
M.Douda

Reputation: 574

The upper formula calculates average of -3 and -2 as it includes only the numbers.

The lower formula calculates average of -3, -2 and 0, as the formula that's included in it has a result of FALSE, which is equivalent of 0.

This might be easier to understand with an example.

enter image description here

There's 6 in cell B1, and simple ISBLANK() formula in the rest of the column.

B12 Formula: =AVERAGE(B1:B10)

C12 Formula: =AVERAGE(6;ISBLANK(B2);ISBLANK(B3);ISBLANK(B4);ISBLANK(B5);ISBLANK(B6);ISBLANK(B7);ISBLANK(B8);ISBLANK(B9);ISBLANK(B10))

First formula sees only the number 6 and ignores anything that looks like a text, second formula gets the value of FALSE ("0") before it becomes text and counts with it in the average.

Upvotes: 1

Related Questions