Min, Max, Median & Average cells based on other cell's criteria

John Steel

New member
Joined
Aug 16, 2014
Messages
7
Reaction score
0
Points
0
I'm trying to find the statistics for the current rent based on the type and size of property in the attached worksheet. I know I can use sumif and averageif, but no such functions exist for min and max. Therefore I've used the same basic formula with variations for the statistic needed for all four statistics:

=MIN(IF((SW_DW=$R$8),Current_Rent))

$R$8 is populated by a pull down menu of the unique values in the SW_DW named range. I'm using the pull downs since I want to be able to change what type of property (SW_DW) or what size (Size) for the columns in the table.

The values are not being calculated for all cells and they don't reliably recalculate when I change the entry in the pull down menu above the summary table. A picture of the worksheet and the excel file are linked below.

excel snip.jpg

View attachment Rent Roll.xlsx

Anyone have any ideas?
 
Use this array formula to ignore blanks in the rent

=MIN(IF((SW_DW=R$8)*(Current_Rent<>""),Current_Rent))
 
Back
Top