User Defined Function MAXIF and MINIF

Hi, I know that you can do a maxif with as an array as shown:

=max(if($B$1:$G$1="Rt",$B3:$G3))

I am having problems with that because of teh "Ctrl+Shift+Enter". We have too many cells to do this for. Is there anyway to write a function in VBA that does this?

If you have that formula in a continous range down a column then you shouldn't need to use CTRL+SHIFT+ENTER for each cell, just put the formula in H3, use CSE then drag down column as far as needed.

If you have continuous data in the adjacent column you can just "double click" the "fill handle" on the bottom right of the cell and formula will automaticaly fill down as far as you have data

I'll try that, what if the feeding data changes, do I still need a Control Shift Enter every time?

You would only need to CSE it if the formula changes, not just if the feeding data changes.

HI

The following code will have the same effect as hitting shift ctrl enter

Please Login or Register  to view this content.

So if you can loop through the relevant cells you can convert them to array formulas.

Regards

Jeff

You can use this function
Function SabseZyada(Kya As String, Kahan As Range)
Arr = Range(Kahan.Address)
For i = 1 To UBound(Arr, 1)
If Arr(i, 1) = Kya And SabseZyada < Arr(i, 2) Then SabseZyada = Arr(i, 2)
Next i
End Function

usage as =SabseZyada(A2,Q2:R3) ' First is what to look at and next is range the whole range