Average If - averaging cells based on value of another cell
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! |
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! |
Average If - averaging cells based on value of another cell
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! |
Average If - averaging cells based on value of another cell
Sorry, I just meant that the range C5:C60 has the criteria, not that it's a
sum. |
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! |
Average If - averaging cells based on value of another cell
You're welcome, Jarod - thanks for feeding back.
Pete "Jarod" wrote in message ... 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! |
All times are GMT +1. The time now is 01:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com