Results 1 to 2 of 2

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Click image for larger version. 

Name:	excel snip.jpg 
Views:	12 
Size:	93.8 KB 
ID:	2834

    Rent Roll.xlsx

    Anyone have any ideas?

  2. #2
    Use this array formula to ignore blanks in the rent

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •