Averaging only some rows

[barman]

I have an application where I want to calculate the average of several
fields, but only in those rows where the second column is blank. For
example:

(A3)1 (A4)<blank> (A5)10 (A6)0

(B3)1 (B4)<blank> (B5)20 (B6)2

(C3)50 (C4)some text (C5)500 (C6)0

(D3) 1 (D4)<blank> (D5)15 (D6)4

should give me for a row of averages those calculated for all rows
except C - i.e., 1, 15, 2. I appreciate any suggestions on how I can
design this Excel 2007 solution. Thanks!

 

[Bob Phillips]

Do you mean

=AVERAGE(IF(A46="",A35))

as an array formula, commit with Ctrl-Shift-Enter not just Enter

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[barman]

Thanks. I wasn't aware of array formulae and will read about them.
Sorry for expressing my problem with the confusion of rows and
columns.

Dilip

 

[barman]

This is pretty neat! I wish that it were easier to remember that one
has to hit Cntrl-Shift-Enter - but when one updates the formula and
uses just Enter, there will be a clear reminder.

How do I handle this when my rows grow over time? How would the array
formula

=AVERAGE(IF($B$3:$B$6="",(A3:A6)))

to calculate the selective average of the A column, put into cell A1,
work when I initially have data in rows 3-6 but over time will have
data in rows 3-10, 3-300, and in general an indeterminate more rows?
Thanks!

A B C D
1
2
3 1 10 0
4 1 20 2
5 50 sometext 500 0
6 1 15 4
7 ***

 

[Herbert Seidenberg]

Use Pivot Table.
No formulas needed.
Source data range, PT range
all dynamic:
http://www.savefile.com/files/1747957