Average If - averaging cells based on value of another cell
Jarod,
I'm confused about the criteria so what this does is if the sum of C5:C60
=C62 then it averages your non contiguous range.
=IF(SUM(C5:C60)=C62,AVERAGE(IF(ISNUMBER(MATCH(ROW( O1:O57),{5,6,7,8,9,16,17,18,19,20,21,28,29,30,31,3 2,33,41,42,43,44,45,52,53,54,55,56,57},0)),O1:O57) ),"")
It's an array so commit with Ctrl+Shift+Enter NOT just enter. If you've done
it correctly then Excel will put curly brackets around it {}. You cannot type
these yourself.
Mike
"Jarod" wrote:
I have a spreadsheet where O:62 and O:63 are products of a formula in their
rows. The cells in between are segmented, so I can't do a simple O1:0:60,
it's more like O5:O9,O16:O21,etc
So, if C5:C60=C62, I need the average of O5:O9, O16:O21, O28:O33, O41:O45, &
O52:O57. I can't just do O5:O57 because in between the sections are other
averages.
Thanks in advance!
|