PDA

View Full Version : get min and max from array...



sal21
09-02-2007, 03:05 PM
in column A have a value related column D.
In effect in D are the value of Principal Agency and in column A are the value of Sub Agency of Principal Agency.
Now based column D how to ,(with a function or macro) get and store in a variable the value of min and max of Sub Agengy?

Example:
for all 4580 in column D get the min and max from column A.
In this case the min is 4500 the max is 6552
The name of variable is MIN_Agency and MAX_Agency
Tks.

Shazam
09-02-2007, 09:06 PM
I notice your numbers are formatted as text so I had to use a couple more functions to get the values that you desire.


Input formula in cell F2 and copy down.

=MIN(IF(TRIM($D$2:$D$1000)=E2,$A$2:$A$1000+0))


Input formula in cell G2 and copy down.

=MAX(IF(TRIM($D$2:$D$1000)=E2,$A$2:$A$1000+0))

Both formulas are an-arrays need to hold down:

Ctrl,Shift,Enter


Hope it helps!

sal21
09-03-2007, 12:11 AM
EXCELLENT FORMULA!
tKS.
But with macro or function?
Because i want to store the min and max value in a var....
Tks.

Shazam
09-03-2007, 07:59 AM
EXCELLENT FORMULA!
tKS.
But with macro or function?
Because i want to store the min and max value in a var....
Tks.

Sorry sal21 I'm not atune with VBA. I thought when you say function you meant formula but you want a UDF sorry I can't help you there. There are plenty of people here could help you with that. Maybe try to do a macro record and double click in the cell that contains the formulas and you'll get your macro.