Wondering if anyone has ideas to get this done...
Col A Col B
Cat 100
Cat 225
Cat 175
Cat 179
Cat 180
Cat 125

Now, I want to use a formula to give me the average of the top 3rd group,
average of the middle 3rd, and average of the bottom 3rd (top 3rd here would
be the 180 & 225, mid 3rd would be 175 & 179, bottom 3rd would be 100 & 125.
It has to also look at the "Cat" label. So, average top 3rd when = cat, etc.

Let me know of any questions....thanks in advance!

Top 3rd group: =AVERAGE(LARGE(IF(A1:A12="Cat",B1:B12),{1,2}))
Mid 3rd group: =AVERAGE(LARGE(IF(A1:A12="Cat",B1:B12),{3,4}))
Bottom 3rd group: =AVERAGE(LARGE(IF(A1:A12="Cat",B1:B12),{5,6}))

ctrl+shift+enter, not just enter
adjust your range to suit


"deeds" wrote:

Wondering if anyone has ideas to get this done...
Col A Col B
Cat 100
Cat 225
Cat 175
Cat 179
Cat 180
Cat 125

Now, I want to use a formula to give me the average of the top 3rd group,
average of the middle 3rd, and average of the bottom 3rd (top 3rd here would
be the 180 & 225, mid 3rd would be 175 & 179, bottom 3rd would be 100 & 125.
It has to also look at the "Cat" label. So, average top 3rd when = cat, etc.

Let me know of any questions....thanks in advance!

Thanks...is there any way to use a cell reference in place of the {1,2}.
i.e. I want to be able to have a cell reference for the "2" above. Any
ideas? Thanks.

"Teethless mama" wrote:

Top 3rd group: =AVERAGE(LARGE(IF(A1:A12="Cat",B1:B12),{1,2}))
Mid 3rd group: =AVERAGE(LARGE(IF(A1:A12="Cat",B1:B12),{3,4}))
Bottom 3rd group: =AVERAGE(LARGE(IF(A1:A12="Cat",B1:B12),{5,6}))

ctrl+shift+enter, not just enter
adjust your range to suit


"deeds" wrote:

Wondering if anyone has ideas to get this done...
Col A Col B
Cat 100
Cat 225
Cat 175
Cat 179
Cat 180
Cat 125

Now, I want to use a formula to give me the average of the top 3rd group,
average of the middle 3rd, and average of the bottom 3rd (top 3rd here would
be the 180 & 225, mid 3rd would be 175 & 179, bottom 3rd would be 100 & 125.
It has to also look at the "Cat" label. So, average top 3rd when = cat, etc.

Let me know of any questions....thanks in advance!