Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
get min and max from array...
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200708/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
get min and max from array...
=MIN(IF(D1:D100=4552,A1:A1000))
and =MAX(IF(D1:D100=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" <u13846@uwe wrote in message news:778442fda19a7@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200708/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
get min and max from array...
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? Bob Phillips wrote: =MIN(IF(D1:D100=4552,A1:A1000)) and =MAX(IF(D1:D100=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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
get min and max from array...
Give these a try...
Maximum.... =TEXT(SUMPRODUCT(MAX((D1:D1000="4580")*A1:A1000)), "@") Minimum... =TEXT(10000-SUMPRODUCT(MAX((D1:D1000="4580")*(10000-A1:A1000))),"@") Rick "gssitaly via OfficeKB.com" <u13846@uwe wrote in message news:7792712991837@uwe... 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? Bob Phillips wrote: =MIN(IF(D1:D100=4552,A1:A1000)) and =MAX(IF(D1:D100=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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
get min and max from array...
ERROR=#VALORE!
Rick Rothstein (MVP - VB) wrote: Give these a try... Maximum.... =TEXT(SUMPRODUCT(MAX((D1:D1000="4580")*A1:A1000)) ,"@") Minimum... =TEXT(10000-SUMPRODUCT(MAX((D1:D1000="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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
get min and max from array...
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 "gssitaly via OfficeKB.com" <u13846@uwe wrote in message news:7794f7e36e6ec@uwe... ERROR=#VALORE! Rick Rothstein (MVP - VB) wrote: Give these a try... Maximum.... =TEXT(SUMPRODUCT(MAX((D1:D1000="4580")*A1:A1000) ),"@") Minimum... =TEXT(10000-SUMPRODUCT(MAX((D1:D1000="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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming |