88. Find First non Numeric Value in a Range
=IFERROR(INDEX(A1:A100,MATCH(1,INDEX(--ISTEXT(A1:A100),,),0)),””)
89. Find Last non Numeric Value in a Range
=IFERROR(LOOKUP(REPT("z",255),A1:A100),””)
90. Find Last Used Value in a Range
= IFERROR(LOOKUP(2,1/(A1:A100<>""),A1:A100),””)
91. MAXIF
Note – Excel 2016 has introduced MAXIFS function
Suppose you want to find the Maximum Sales for East Region i.e. MAXIF
=SUMPRODUCT(MAX((A2:A100="East")*(B2:B100)))
=AGGREGATE(14,6,($A$2:$A$100="East")*($B$2:$B$100),1)
SUMPRODUCT formula is faster than the second formula.
92. MINIF
Note – Excel 2016 has introduced MINIFS function
Suppose you want to find the Minimum Sales for West Region i.e. MINIF
=AGGREGATE(15,6,1/($A$2:$A$10="West")*($B$2:$B$10),1)
But the above formula will not ignore blanks or 0 values in your range. If you want to ignore
0 values /blanks, in your range, then use following formula
