Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
Hi
I have a spreadsheet that contains text and time information such as below: a b c 1 north high 5:21 2 south low 6:42 3 north low 7:14 4 north high 3:56 5 east low 2:14 6 north low 2:02 7 north high 2:37 All of the information in the spreadsheet is the result of formulas from other data (in other sheets). I need a formula that will provide the maximum time (in Col C) in a row where (for example) the Col A is "north" and Col B is "high". Any help would be greatly appreciated. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
=SUMPRODUCT(MAX((A1:A7="north")*(B1:B7="high")*C1: C7))
-- Gary''s Student - gsnu200785 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
Add a header row to your data:
Compass Level Time north high 5:21 south low 6:42 north low 7:14 north high 3:56 east low 2:14 north low 2:02 north high 2:37 Create a criteria range: Compass Level north high In this formula, I used E1:F2 for the criteria range. Enter formula: =DMAX(A1:C8,3,E1:F2) "mwd" wrote: Hi I have a spreadsheet that contains text and time information such as below: a b c 1 north high 5:21 2 south low 6:42 3 north low 7:14 4 north high 3:56 5 east low 2:14 6 north low 2:02 7 north high 2:37 All of the information in the spreadsheet is the result of formulas from other data (in other sheets). I need a formula that will provide the maximum time (in Col C) in a row where (for example) the Col A is "north" and Col B is "high". Any help would be greatly appreciated. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
DMAX makes it easy to change criteria to find other combinations of values...
But you could also use the following array formula: =MAX(C2:C8*((A2:A8="north")*(B2:B8="high"))) Enter as array with ctrl+shift+enter "mwd" wrote: Hi I have a spreadsheet that contains text and time information such as below: a b c 1 north high 5:21 2 south low 6:42 3 north low 7:14 4 north high 3:56 5 east low 2:14 6 north low 2:02 7 north high 2:37 All of the information in the spreadsheet is the result of formulas from other data (in other sheets). I need a formula that will provide the maximum time (in Col C) in a row where (for example) the Col A is "north" and Col B is "high". Any help would be greatly appreciated. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
=MAX(INDEX((A1:A7="north")*(B1:B7="high")*C1:C7,0) )
"mwd" wrote: Hi I have a spreadsheet that contains text and time information such as below: a b c 1 north high 5:21 2 south low 6:42 3 north low 7:14 4 north high 3:56 5 east low 2:14 6 north low 2:02 7 north high 2:37 All of the information in the spreadsheet is the result of formulas from other data (in other sheets). I need a formula that will provide the maximum time (in Col C) in a row where (for example) the Col A is "north" and Col B is "high". Any help would be greatly appreciated. Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
Just as a point of information, can anyone out there with access to Fast
Excel or other similar software determine which of these formulas would be faster and less resource intensive? Say you size them to 7,000 rows. Gary's Sumproduct: =SUMPRODUCT(MAX((A1:A7000="north")*(B1:B7000="high ")*C1:C7000)) Tm's Index: =MAX(INDEX((A1:A7000="north")*(B1:B7000="high")*C1 :C7000,0)) OR, a standard *array* entered formula: =MAX((A1:A7000="north")*(B1:B7000="high")*C1:C7000 ) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "mwd" wrote in message ... Hi I have a spreadsheet that contains text and time information such as below: a b c 1 north high 5:21 2 south low 6:42 3 north low 7:14 4 north high 3:56 5 east low 2:14 6 north low 2:02 7 north high 2:37 All of the information in the spreadsheet is the result of formulas from other data (in other sheets). I need a formula that will provide the maximum time (in Col C) in a row where (for example) the Col A is "north" and Col B is "high". Any help would be greatly appreciated. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
http://img158.imageshack.us/img158/5...lctimesme8.jpg
Times based on Charles Williams RangeTimer method: http://msdn2.microsoft.com/en-us/library/aa730921.aspx -- Biff Microsoft Excel MVP "Ragdyer" wrote in message ... Just as a point of information, can anyone out there with access to Fast Excel or other similar software determine which of these formulas would be faster and less resource intensive? Say you size them to 7,000 rows. Gary's Sumproduct: =SUMPRODUCT(MAX((A1:A7000="north")*(B1:B7000="high ")*C1:C7000)) Tm's Index: =MAX(INDEX((A1:A7000="north")*(B1:B7000="high")*C1 :C7000,0)) OR, a standard *array* entered formula: =MAX((A1:A7000="north")*(B1:B7000="high")*C1:C7000 ) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "mwd" wrote in message ... Hi I have a spreadsheet that contains text and time information such as below: a b c 1 north high 5:21 2 south low 6:42 3 north low 7:14 4 north high 3:56 5 east low 2:14 6 north low 2:02 7 north high 2:37 All of the information in the spreadsheet is the result of formulas from other data (in other sheets). I need a formula that will provide the maximum time (in Col C) in a row where (for example) the Col A is "north" and Col B is "high". Any help would be greatly appreciated. Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
Thanks for your effort in this Biff.
I had a suspicion that perhaps that Index formula might be the fastest, but I can see I was way off. Good old Sumproduct appears to be a steady, all-around, workhorse choice. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... http://img158.imageshack.us/img158/5...lctimesme8.jpg Times based on Charles Williams RangeTimer method: http://msdn2.microsoft.com/en-us/library/aa730921.aspx -- Biff Microsoft Excel MVP "Ragdyer" wrote in message ... Just as a point of information, can anyone out there with access to Fast Excel or other similar software determine which of these formulas would be faster and less resource intensive? Say you size them to 7,000 rows. Gary's Sumproduct: =SUMPRODUCT(MAX((A1:A7000="north")*(B1:B7000="high ")*C1:C7000)) Tm's Index: =MAX(INDEX((A1:A7000="north")*(B1:B7000="high")*C1 :C7000,0)) OR, a standard *array* entered formula: =MAX((A1:A7000="north")*(B1:B7000="high")*C1:C7000 ) -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "mwd" wrote in message ... Hi I have a spreadsheet that contains text and time information such as below: a b c 1 north high 5:21 2 south low 6:42 3 north low 7:14 4 north high 3:56 5 east low 2:14 6 north low 2:02 7 north high 2:37 All of the information in the spreadsheet is the result of formulas from other data (in other sheets). I need a formula that will provide the maximum time (in Col C) in a row where (for example) the Col A is "north" and Col B is "high". Any help would be greatly appreciated. Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
7000 rows, 10 iterations
DMAX Array Index Sumproduct 0.000174949 0.000173458 0.000173570 0.000175497 Average 0.000180749 0.000177117 0.000185778 0.000188013 Max 0.000171530 0.000169575 0.000169575 0.000170133 Min 0.000009219 0.000007542 0.000016203 0.000017880 Range Array had the lowest average, tied with Index for the lowest min, and was the most consistent, with the lowest range. Sumproduct had the highest average, the highest max and was the least consistent, with the largest range. Data: DMAX Array Index Sumproduct 0.000171530 0.000169854 0.000169854 0.000171530 0.000179911 0.000176838 0.000172927 0.000170133 0.000172089 0.000173486 0.000171530 0.000174324 0.000174993 0.000172648 0.000172368 0.000178794 0.000180749 0.000174603 0.000169575 0.000173206 0.000171530 0.000172368 0.000172927 0.000175162 0.000172089 0.000173486 0.000185778 0.000172368 0.000174603 0.000177117 0.000175721 0.000177676 0.000175162 0.000169575 0.000172089 0.000188013 0.000176838 0.000174603 0.000172927 0.000173765 "Ragdyer" wrote: Just as a point of information, can anyone out there with access to Fast Excel or other similar software determine which of these formulas would be faster and less resource intensive? Say you size them to 7,000 rows. Gary's Sumproduct: =SUMPRODUCT(MAX((A1:A7000="north")*(B1:B7000="high ")*C1:C7000)) Tm's Index: =MAX(INDEX((A1:A7000="north")*(B1:B7000="high")*C1 :C7000,0)) OR, a standard *array* entered formula: =MAX((A1:A7000="north")*(B1:B7000="high")*C1:C7000 ) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "mwd" wrote in message ... Hi I have a spreadsheet that contains text and time information such as below: a b c 1 north high 5:21 2 south low 6:42 3 north low 7:14 4 north high 3:56 5 east low 2:14 6 north low 2:02 7 north high 2:37 All of the information in the spreadsheet is the result of formulas from other data (in other sheets). I need a formula that will provide the maximum time (in Col C) in a row where (for example) the Col A is "north" and Col B is "high". Any help would be greatly appreciated. Thanks. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
Those look like recalculaion times. I get similar results when I test the
recalculation time. I tested the *initial* calculation times. -- Biff Microsoft Excel MVP "BoniM" wrote in message ... 7000 rows, 10 iterations DMAX Array Index Sumproduct 0.000174949 0.000173458 0.000173570 0.000175497 Average 0.000180749 0.000177117 0.000185778 0.000188013 Max 0.000171530 0.000169575 0.000169575 0.000170133 Min 0.000009219 0.000007542 0.000016203 0.000017880 Range Array had the lowest average, tied with Index for the lowest min, and was the most consistent, with the lowest range. Sumproduct had the highest average, the highest max and was the least consistent, with the largest range. Data: DMAX Array Index Sumproduct 0.000171530 0.000169854 0.000169854 0.000171530 0.000179911 0.000176838 0.000172927 0.000170133 0.000172089 0.000173486 0.000171530 0.000174324 0.000174993 0.000172648 0.000172368 0.000178794 0.000180749 0.000174603 0.000169575 0.000173206 0.000171530 0.000172368 0.000172927 0.000175162 0.000172089 0.000173486 0.000185778 0.000172368 0.000174603 0.000177117 0.000175721 0.000177676 0.000175162 0.000169575 0.000172089 0.000188013 0.000176838 0.000174603 0.000172927 0.000173765 "Ragdyer" wrote: Just as a point of information, can anyone out there with access to Fast Excel or other similar software determine which of these formulas would be faster and less resource intensive? Say you size them to 7,000 rows. Gary's Sumproduct: =SUMPRODUCT(MAX((A1:A7000="north")*(B1:B7000="high ")*C1:C7000)) Tm's Index: =MAX(INDEX((A1:A7000="north")*(B1:B7000="high")*C1 :C7000,0)) OR, a standard *array* entered formula: =MAX((A1:A7000="north")*(B1:B7000="high")*C1:C7000 ) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "mwd" wrote in message ... Hi I have a spreadsheet that contains text and time information such as below: a b c 1 north high 5:21 2 south low 6:42 3 north low 7:14 4 north high 3:56 5 east low 2:14 6 north low 2:02 7 north high 2:37 All of the information in the spreadsheet is the result of formulas from other data (in other sheets). I need a formula that will provide the maximum time (in Col C) in a row where (for example) the Col A is "north" and Col B is "high". Any help would be greatly appreciated. Thanks. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX value matching multiple criteria
DMAX Array Index Sumproduct
0.0240969 0.0240885 0.0243997 0.0243215 0.0127678 0.0128463 0.0129556 0.0126991 0.0128880 0.0128690 0.0126720 0.0128310 0.0127262 0.0127622 0.0129514 0.0129377 0.0239782 0.0129301 0.0126226 0.0243023 0.0127907 0.0244173 0.0242073 0.0127064 0.0126882 0.0126583 0.0127625 0.0127427 0.0126734 0.0129637 0.0127756 0.0129603 0.0241332 0.0126843 0.0127209 0.0243031 0.0127002 0.0127251 0.0127625 0.0126938 0.0161443 0.0150945 0.0150830 0.0162498 Average 0.0241332 0.0244173 0.0243997 0.0243215 Max 0.0126734 0.0126583 0.0126226 0.0126938 Min 0.0114598 0.0117590 0.0117772 0.0116277 Range testing *initial* calculations I'm sorry - I should have been specific about what was being tested... "T. Valko" wrote: Those look like recalculaion times. I get similar results when I test the recalculation time. I tested the *initial* calculation times. -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Matching Text with With Certain Criteria | Excel Worksheet Functions | |||
return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Finding One Value, Matching Three Criteria | Excel Discussion (Misc queries) | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions |