Average Selected Cells From a Range

[Guest]

Good morning --
I would like to average the values of cells B14:R14 if the corresponding
value in the range B6:R6 is "Y". I've searched these threads, tried
'sumproduct' and CSE formulas, but just can't quite get over the hump. Any
assistance offered would be greatly appreciated.

Thanks in advance.

 

[Peo Sjoblom]

=AVERAGE(IF(B6:R6="Y",B14:R14))

entered with ctrl + shift & enter

if B6:R6 can have Y and B14:R14 can be blank and you don't want count that
as 0

=AVERAGE(IF(B6:R6="Y",IF(B14:R14<>"",B14:R14)))

also array entered

 

[Guest]

You absolutely be da' man! Thanks very much.

It occurs to me that the CSE formulas, and the power of the 'embedded logic'
inside things like sumproduct are so powerful, and I wonder why the help
isn't better for these kinds of features...

Thanks again.

 

[Peo Sjoblom]

Thanks for the feedback, the help in Excel is really bad, the last that was
decent was back in 97 and it didn't dwell on these matters,. There are
websites that do this

for array formulas

http://www.cpearson.com/excel/ArrayFormulas.aspx


for sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
Regards,

Peo Sjoblom