Array Formulas

  • Thread starter Thread starter AK
  • Start date Start date
A

AK

According to the Excel 2000 help feature, Array Formulas
are supposed to return a calc based on a condition you
specify in a particular corresponding row. In the below
example, the result should be the average of 5,7, and 9.
Ex.
=AVERAGE(IF(C1:C6="A",D1:D6))
A 5
B 6
A 7
B 8
A 9
B 10

My question is two-fold:
1. If one of the cells is blank, ex. if 5 wasn't there,
why does it average that cell as a zero even thought there
would be no cell contents?
2. Can the COUNTA function be used in this scenario (ie.
in place of AVERAGE)... I couldn't get it to work.
Many thanks
A. Karesh
 
1. Because functions always return values - if a cell is empty, the
value is interpreted as 0 in numeric contexts and the null string in
text contexts.

Try (array-entered):

=AVERAGE(IF((C1:C6="A")*(D1:D6<>""),D1:D6))
 
AK,

Blanks are treated as zeroes in array formulas.

Use the Array formula - entered with Ctrl-Shift-Enter:

=AVERAGE(IF(C1:C6="A",IF(D1:D6<>"",D1:D6)))

HTH,
Bernie
MS Excel MVP
 
Blanks are treated as zeroes in array formulas.
Click to expand...

No they're not. Using your own formula below, if blanks were treated as zeros in
array formula, D1:D6<>"" would return TRUE for blank cells just like it would
for cells evaluating to zero. It evaluates to FALSE, so your statement is wrong.

It's IF() that's to blame. With some blank cells in D1:D6 corresponding to "A"
entries in col C, in a blank cell type the formula =IF(C1:C6="A",D1:D6) and
press [F9]. The blanks in col D corresponding to "A" in col C show as 0. The
so it must coerce <blank> values said:
Use the Array formula - entered with Ctrl-Shift-Enter:

=AVERAGE(IF(C1:C6="A",IF(D1:D6<>"",D1:D6)))
Click to expand...
...

This works as written, but JE's formula

=AVERAGE(IF((C1:C6="A")*(D1:D6<>""),D1:D6))

has a big advantage: any mismatch in shape or size between the col C and col D
ranges would always cause JE's formula to return an error value. Bernie's
formula could return numbers sometimes, error values other times. For example,
with the following data in C1:D6,

A 5
B 6
A
B 8
A 0
B 10

the mistyped formula

=AVERAGE(IF(C1:C6="A",IF(D1:D5<>"",D1:D5)))

returns 2.5 (the correct result under the circumstances), but change C6 from B
to A, and the formula returns #N/A. Formulas that *always* trap misreference
errors are *always* better than formulas that produce mixed results. So this
isn't a case of more than one way to do something. Use JE's formula.
 
Wow, this is great help. I now have the average function
working perfectly. To my original email, if I want to use
use the COUNTA function, why can't I simply replace
AVERAGE w/ COUNTA. What I actually want to do in this
particular spreadsheet is count how many times the
word "Yes" appears in column O given the name in column K
= Bob. (ie. If cell k5 = Bob then count the Yes in the
correspnding cell in column O, then if k10 = Bob also,
count that Yes as well and so on) Giving me the total
number of "Yes" where name = Bob.

Thanks to everyone for their insights.
A. Karesh
-----Original Message-----
Blanks are treated as zeroes in array formulas.
Click to expand...

No they're not. Using your own formula below, if blanks were treated as zeros in
array formula, D1:D6<>"" would return TRUE for blank cells just like it would
for cells evaluating to zero. It evaluates to FALSE, so your statement is wrong.

It's IF() that's to blame. With some blank cells in D1:D6 corresponding to "A"
entries in col C, in a blank cell type the formula =IF (C1:C6="A",D1:D6) and
press [F9]. The blanks in col D corresponding to "A" in col C show as 0. The
point here is that IF() can't return <blank>, so it must
Click to expand...
 
Back
Top