1

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

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70
ConorCK
  • 11
  • 3
  • The `AVERAGE` function ignores logical values only if they are in a cell reference (as in your first formula). It does NOT ignore the logical value in your 2nd formula. – Ron Rosenfeld Jul 25 '18 at 11:22
  • Not sure what you want to do, but if you remove the `IFERROR(…` wrapper and allow `A2:A9` to return the actual errors, you could Average the non-error results with `=AGGREGATE(1,6,A2:A9)` or more simply: `=SUMPRODUCT((D2:D9=G2:G9)*E2:E9)/SUMPRODUCT(--(D2:D9=G2:G9))` – Ron Rosenfeld Jul 25 '18 at 11:36

1 Answers1

1

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.

M.Douda
  • 564
  • 1
  • 7
  • 18