get min and max from array...

[gssitaly]

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.

Note: all column are in Text format

column A column D
0500 0580
0501 0580
0502 0580
0503 0580
0504 0580
0505 0580
0506 0580
0507 0580
0543 0580
0546 0580
0549 0580
0552 0580
0555 0580
0558 0580
0561 0580
0564 0580
0567 0580
0570 0580
0576 0580
0579 0580
0580 0580
0582 0580
1700 4780
1701 4780
1702 4780
1703 4780
1704 4780
1743 4780
1746 4780
1749 4780
1752 4780
1755 4780
1758 4780
2000 2380
2043 2380
2044 2380
2046 2380
2049 2380
2052 2380
2055 2380
2300 2380
2301 2380
2302 2380
2343 2380
2346 2380
2349 2380
2352 2380
2355 2380
2380 2380
2900 0580
2911 0580
2943 0580
2946 0580
2949 0580
3700 0580
3701 0580
3743 0580
3752 0580
3755 0580
3758 0580
4200 4780
4201 4780
4210 4780
4243 4780
4246 4780
4500 4580
4501 4580
4502 4580
4503 4580
4504 4580
4505 4580
4506 4580
4507 4580
4508 4580
4509 4580
4510 4580
4511 4580
4512 4580
4513 4580
4514 4580
4515 4580
4516 4580
4532 4580
4534 4580
4535 4580
4536 4580
4543 4580
4546 4580
4549 4580
4552 4580
4555 4580
4556 4580
4557 4580
4558 4580
4561 4580
4562 4580
4564 4580
4567 4580
4569 4580
4573 4580
4576 4580
4579 4580
4580 4580
4582 4580
4700 4780
4701 4780
4702 4780
4703 4780
4704 4780
4705 4780
4706 4780
4743 4780
4746 4780
4749 4780
4752 4780
4755 4780
4758 4780
4761 4780
4773 4780
4775 4780
4776 4780
4780 4780
5900 2380
5901 2380
5902 2380
5943 2380
5946 2380
6500 4580
6501 4580
6502 4580
6543 4580
6546 4580
6549 4580
6552 4580
6800 0580
6801 0580
6803 0580
6804 0580
6805 0580
6811 0580
6843 0580
6846 0580

 

[Bob Phillips]

=MIN(IF(D1100=4552,A1:A1000))

and

=MAX(IF(D1100=4552,A1:A1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[gssitaly via OfficeKB.com]

Tks Bob...
But work only if the column A and D are in General or number, my two column
are in Text format, and not work?! in other case how to stora the the result
in variable if i use a formula?

=MIN(IF(D1100=4552,A1:A1000))

and

=MAX(IF(D1100=4552,A1:A1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

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

[quoted text clipped - 153 lines]

6843 0580
6846 0580

 

[Rick Rothstein \(MVP - VB\)]

Give these a try...

Maximum....
=TEXT(SUMPRODUCT(MAX((D11000="4580")*A1:A1000)),"@")

Minimum...
=TEXT(10000-SUMPRODUCT(MAX((D11000="4580")*(10000-A1:A1000))),"@")

Rick

Tks Bob...
But work only if the column A and D are in General or number, my two
column
are in Text format, and not work?! in other case how to stora the the
result
in variable if i use a formula?

=MIN(IF(D1100=4552,A1:A1000))

and

=MAX(IF(D1100=4552,A1:A1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

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

[quoted text clipped - 153 lines]

6843 0580
6846 0580

 

[gssitaly via OfficeKB.com]

ERROR=#VALORE!

Give these a try...

Maximum....
=TEXT(SUMPRODUCT(MAX((D11000="4580")*A1:A1000)),"@")

Minimum...
=TEXT(10000-SUMPRODUCT(MAX((D11000="4580")*(10000-A1:A1000))),"@")

Rick

Tks Bob...
But work only if the column A and D are in General or number, my two

[quoted text clipped - 22 lines]

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure what to say... those formulas work fine on my system. What I
did is copy the data from your first posting and paste it into A1 (this
filled column A with two values combined into one for each cell downward in
column A); then I used Data/TextToColumns to move the data into two columns
(A and B) making sure to designate each column as Text; then I cut column B
from the sheet and pasted it into column D. At this point, your values were
all text residing in columns A and D. I then copy/pasted my two formulas
into any two cells and they produced the answers you indicated they should.
If it matters any, the cells I pasted my formulas in were formatted to
General before I pasted them in.

Rick

ERROR=#VALORE!

Give these a try...

Maximum....
=TEXT(SUMPRODUCT(MAX((D11000="4580")*A1:A1000)),"@")

Minimum...
=TEXT(10000-SUMPRODUCT(MAX((D11000="4580")*(10000-A1:A1000))),"@")

Rick

Tks Bob...
But work only if the column A and D are in General or number, my two

[quoted text clipped - 22 lines]

6843 0580
6846 0580