Average cells meeting a criteria

[msampson]

I have a table that is 6 rows by 30, so 180 cells. I was able to do a lookup so that I now have a column of values and next to it a column which says which cell it belongs to. I can do a countif on the second column to see how many values in each cell. But what I want to do is the average value (from the first column) of each cell (listed in the second column).
How can I do averageif to only average the values belonging to each cell.

A....B
10..6
2....120
6.....3
12....6

so in this case it would only average the 10 and 12 in the first and last rows.
I've made a new column that has 180 rows to correspond to each cell so I'd like to copy down the formula to get the average in each of the 180 cells.
Maureen

 

[Aladin Akyurek]

Care to clarify your set up by posting a scaled-down example along with the desired results for that sample?

 

[msampson]

I figured out how to use the averageif function. Now, is there a way to calculate median with the same type of criteria? I see countif and sumif but not others like that.

 

[Aladin Akyurek]

I figured out how to use the averageif function. Now, is there a way to calculate median with the same type of criteria? I see countif and sumif but not others like that.

Control+shift+enter, not just enter:

=MEDIAN(IF(ConditionRange=Condition,MeadianRange))

 

[msampson]

thank you - this is very helpful.

 

[Aladin Akyurek]

thank you - this is very helpful.

You are welcome.

 

[msampson]

I've been using this a lot in my current project and have adapted it to average and mode. But now I need to use the same conditional criteria to define the range and return the percentilerank of a number from within that range. The formula for a plain Percentilerank has it's own range and then a location of the number that you want the percentilerank for. How can I adapt the above formula to add the one extra parameter?

 

[Aladin Akyurek]

Hi Maureen,

Could you post a small sample along with the desired result for that sample?

 

[msampson]

I found the syntax I needed in this thread
https://www.mrexcel.com/forum/excel-questions/942577-conditional-percentile-rank.html
It was the positioning of the parentheses and commas I needed.