Maxif and Minif

[rajat_kiesquare]

I was wondering if there is some command such as maxif and Minif in exccel. I couldn't find any. Can Chandoo or somebody else suggest me sum alternative to it. I need to get min of a array by group.

 

[NARAYANK991]

Hi Rajat ,


I am not sure I have understood your question , but let me suggest this :


=SMALL(IF(A1:A20<10,(A1:A20),MAX(A1:A20)),1)


entered as an array formula ( using CTRL SHIFT ENTER ) , will give you the smallest value in the list A1:A20 , which meets the criterion &#62;10 ; any value less than or equal to 10 will not be used to evaluate the formula.


Similarly ,


=LARGE(IF(A1:A20>10,(A1:A20),MIN(A1:A20)),1)


entered as an array formula ( using CTRL SHIFT ENTER ) , will give you the largest value in the list A1:A20 , which meets the criterion &#60;10 ; any value greater than or equal to 10 will not be used to evaluate the formula.


Narayan

 

[srinidhi]

If you just want to find the max & Min in a range without any condition, you can use this simple formula:


LARGE(range,rank)

SMALL(range,rank)


Here rank means, you want the 1st largest number, 2nd largest number........

 

[Luke M]

Simplifying Narayank's formulas a little:

=MAX(IF(A1:A20&#62;10,A1:A20))


=MIN(IF(A1:A20&#60;10,A1:A20))

 

[NARAYANK991]

Hi Luke ,


Thanks.


Narayan


EDIT : I think the formula is an array formula ; if it is entered as a normal formula , its result is not correct.

 

[Luke M]

Yep, still an array formula. Thanks for catching that, I should have made it clearer.