Prev: Find 2 lowest point to form a trendline
Next: Adding dates in columns
From: T. Valko on 12 Apr 2010 22:03
> Why the IF call?
>F <blank>

Define blank.

If blank is an EMPTY cell then ISNUMBER prevents the empty cell from being
included in the average as numeric 0. If the logical test is TRUE the
corresponding cell in the value_if_true argument is included in the average
and if that cell is empty it's evaluated as numeric 0.

If BLANK is a formula blank then it would be ignored as a text entry in an
array reference.

Are you sure you just didn't have a "senior moment"? It's ok, I have them
sometimes and I'm not technically a senior just yet!

--
Biff
Microsoft Excel MVP


"Harlan Grove" <hrlngrv(a)gmail.com> wrote in message
news:696fec3c-cdbc-408f-ab49-98ebf6bb3d84(a)b23g2000yqn.googlegroups.com...
> "T. Valko" <biffinp...(a)comcast.net> wrote...
>>By missing data points I assume you numbers to average.
> ...
>>=AVERAGE(IF(F52:F107=E5,IF(ISNUMBER(K52:K107),K52:K107)))
> ...
>
> Why the IF call? With the following data in A1:B8,
>
> F 1
> F <blank>
> M 3
> M 4
> F 5
> F 6
> F 7
> M 8
>
> and F in A10, the array formula
>
> =AVERAGE(IF(A1:A8=A10,B1:B8))
>
> returns 4.75 (as per specs) rather than 3.8. The AVERAGE function
> ignores cells which don't contain numbers.


First  |  Prev  | 
Pages: 1 2
Prev: Find 2 lowest point to form a trendline
Next: Adding dates in columns