average function and error/missing values - inconsistant behavior!

[THOMAS CONLON]

Average has what seems to be an odd (inconsistant) behavior to me. I want
to be able to have a value of "" within the average function arguments, and
for it to still return a result.

For instance, if i have a cell with value 1 in it, and a cell with the
following formula in it: "=IF(TRUE,"","")", and i then use Average function,
with range that includes those two cells, result of Average is "1" [which is
correct, and is what i want].

But, if i have the following use of Average function:
=Average(1,IF(TRUE,"","")), Average function returns #VALUE.

In my mind, those two results seem to be inconsistant!

Is there a way to get around that second behavior, and have that use of
Average function return "1", just like the first use?

Thanks, tom

 

[Bernard Liengme]

SUM behaves the same way. We cannot expect programmer to get everything
right!

Let's take a more concrete example. When E22 is greater than 10 would what
the average of (a1:A10 plus E22) but otherwise just the average of A1:A10.
As you point out, the formula =AVERAGE(A1:A6,IF(E22>10,E22,"")) returns
#VALUE! when the IF results in a null string.
But very often you can avoid IF. This gives the correct result when E22 has
a numeric value or is blank =SUM(A1:A6,E22*(E22>10))/(COUNT(A1:A6)+(E22>10))
best wishes

 

[Niek Otten]

Hi Tom,

I agree this is not logical. But it is to specification; the rule of ignoring blanks applies to arrays and reference arguments
only, not to "numbers". And arguments not being numbers just aren't allowed.
Nice? I don't think so. I'm curious what others think about it!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Average has what seems to be an odd (inconsistant) behavior to me. I want
| to be able to have a value of "" within the average function arguments, and
| for it to still return a result.
|
| For instance, if i have a cell with value 1 in it, and a cell with the
| following formula in it: "=IF(TRUE,"","")", and i then use Average function,
| with range that includes those two cells, result of Average is "1" [which is
| correct, and is what i want].
|
| But, if i have the following use of Average function:
| =Average(1,IF(TRUE,"","")), Average function returns #VALUE.
|
| In my mind, those two results seem to be inconsistant!
|
| Is there a way to get around that second behavior, and have that use of
| Average function return "1", just like the first use?
|
| Thanks, tom
|
|

 

[Harlan Grove]

THOMAS CONLON wrote...
....

But, if i have the following use of Average function:
=Average(1,IF(TRUE,"","")), Average function returns #VALUE.

In my mind, those two results seem to be inconsistant!

Fine, but this is how Excel works. Either adapt your mind to Excel's
immutable reality or use something else.

What's going on is that Excel treats values from cell references
differently than values from expressions in its aggregation functions
(other than COUNT and COUNTA). This was a design decision Microsoft
made back in the mid-1980s for near (but not exact) compatibility with
the semantics of Lotus 123's aggregation functions. 123 *always* treats
cells evaluating as text as zeros in numeric contexts, and *always*
treats text arguments as numeric zeros in numeric functions. Excel
follows the former 123 semantics but not the latter.

If cell A1:A3 contained 1, 2 and "foo", respectively, this is what
Excel and 123 would return.

123: @AVG(A1..A3) -> 1 that is, equivalent to @AVG(1,2,0)
123: @AVG(A1,A2,A3) -> 1 also equivalent to @AVG(1,2,0)
123: @AVG(1,2,"foo") -> 1 ditto

Excel: =AVERAGEA(A1:A3) -> 1 that is, equivalent to =AVERAGE(1,2,0)
Excel: =AVERAGEA(A1,A2,A3) -> 1 ditto
Excel: =AVERAGEA(1,2,"foo") -> #VALUE! because "foo" isn't numeric

Change @AVG to @PUREAVG and AVERAGEA to AVERAGE and the common results
would be 1.5, but =AVERAGE(1,2,"foo") would also return #VALUE!.

Excel's SUM, AVERAGE, MIN, MAX, etc functions skip *cells* evaluating
to text. They don't skip expressions returning text, such as "foo" or
LEFT("foobar",3), but they do try to convert such text values to
numbers, so =AVERAGE(1,2,"123") returns 42. It's the text to numeric
conversion that triggers the #VALUE! error because "" doesn't represent
any number as text.

Is there a way to get around that second behavior, and have that use of
Average function return "1", just like the first use?

Yes, there's a simple way to handle this: DON'T USE "", USE {""} -
braces intentional! Excel treats arrays the same as ranges. The formula

=AVERAGE(1,2,IF(TRUE,{""},{""}))

returns 1.5. Consistency? Who needs it?!

To repeat: adapt to how Excel works or use something else.

 

[Harlan Grove]

Niek Otten wrote...
....

Nice? I don't think so. I'm curious what others think about it!

....

Briefly, if they engaged in any a priori design rather than just
hacking Excel together ad hoc, Microsoft designed Excel's semantics
based on focus group feedback. Pity the first Excel focus groups seem
to have been nonprogrammers and/or Multiplan users.

 

[Harlan Grove]

Harlan Grove wrote...
....

differently than values from expressions in its aggregation functions
(other than COUNT and COUNTA). . . .

....

I take it back. COUNT and COUNTA are no different.
=COUNT("123",FALSE,"foo") returns 2. Wonderful!

 

[THOMAS CONLON]

Thanks for the lively discussion and replies. Yes, of course we have to
accept quirks/inconsistancies in Excel, or use something else. Still, it
presents challenges, and thats one good use for this group, for us to share
the little tricks we've learned to get around these things.

Two of the replies can solve the problem. The one I like best, as for me
its the most readable, is from Harlan Grove:
=AVERAGE(1,2,IF(TRUE,{""},{""}))

Also, this would work (from Bernard Liengme). Seems a bit 'hacky' but does
the trick:
=SUM(A1:A6,E22*(E22>10))/(COUNT(A1:A6)+(E22>10))

Thanks much!
Tom

 

[THOMAS CONLON]

THOMAS CONLON wrote...
...

Fine, but this is how Excel works. Either adapt your mind to Excel's
immutable reality or use something else.

What's going on is that Excel treats values from cell references
differently than values from expressions in its aggregation functions
(other than COUNT and COUNTA). This was a design decision Microsoft
made back in the mid-1980s for near (but not exact) compatibility with
the semantics of Lotus 123's aggregation functions. 123 *always* treats
cells evaluating as text as zeros in numeric contexts, and *always*
treats text arguments as numeric zeros in numeric functions. Excel
follows the former 123 semantics but not the latter.

If cell A1:A3 contained 1, 2 and "foo", respectively, this is what
Excel and 123 would return.

123: @AVG(A1..A3) -> 1 that is, equivalent to @AVG(1,2,0)
123: @AVG(A1,A2,A3) -> 1 also equivalent to @AVG(1,2,0)
123: @AVG(1,2,"foo") -> 1 ditto

Excel: =AVERAGEA(A1:A3) -> 1 that is, equivalent to =AVERAGE(1,2,0)
Excel: =AVERAGEA(A1,A2,A3) -> 1 ditto
Excel: =AVERAGEA(1,2,"foo") -> #VALUE! because "foo" isn't numeric

Change @AVG to @PUREAVG and AVERAGEA to AVERAGE and the common results
would be 1.5, but =AVERAGE(1,2,"foo") would also return #VALUE!.

Excel's SUM, AVERAGE, MIN, MAX, etc functions skip *cells* evaluating
to text. They don't skip expressions returning text, such as "foo" or
LEFT("foobar",3), but they do try to convert such text values to
numbers, so =AVERAGE(1,2,"123") returns 42. It's the text to numeric
conversion that triggers the #VALUE! error because "" doesn't represent
any number as text.


Yes, there's a simple way to handle this: DON'T USE "", USE {""} -
braces intentional! Excel treats arrays the same as ranges. The formula

=AVERAGE(1,2,IF(TRUE,{""},{""}))

returns 1.5. Consistency? Who needs it?!

To repeat: adapt to how Excel works or use something else.

 

[ilia]

In the first example, the =IF cell is omitted. In the second example,
you are giving it a non-numeric value that results in the error. You
can read this in the help file.

Note the behavior you will get if you remove both empty strings.