Average If - averaging cells based on value of another cell
I agree, it does need a redesign. Unfortunately, I'm in no position to
approve/undertake it. This formula is what I needed, I won't have criteria
in the "subtotal" row, so it wouldn't count it anyways. Don't know why I
didn't see that.
Thanks!
"Pete_UK" wrote:
Sounds like you could do with a re-design !!
Nevertheless, try this array* formula:
=AVERAGE(IF(C5:C60=$C$62,O5:O60))
*An array formula must be committed using Ctrl-Shift-Enter (CSE)
rather than the usual <Enter. If you do this correctly then Excel
will wrap the formula in curly braces { } when viewed in the formula
bar - you must not type these yourself. Use CSE again if you need to
amend the formula in future.
Hope this helps.
Pete
On Jul 10, 2:59 pm, 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!
|