Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find highest value in a specific data set.
Excel 2003
I need a formula that will tell me the largest number in the "Value" column for a specific "Group". In the sample data below the value for ACME's "USA" group would be "1.5" for their "UK" Group it would be "0.5" "DJ INDUSTRIES "Alpha" group it would be "1.0". Some "Group" Names maybe the same for diffferent companies ("Company"). So the formula needs to identify a change in "company" value as well. Example of Data: "/" = Denotes New Column of Data First Row are Column Headers Company / Group / Product Code / Value / {Column for formula} ACME/USA/3456/0.5 ACME/USA/3456/0.5 ACME/USA/5432/1.0 ACME/USA/9584/1.5 ACME/UK/3456/0.5 ACME/UK/3456/0.5 DJ INDUSTRIES/ALPHA/5432/1.0 DJ INDUSTRIES/ALPHA/3456/0.5 JR ENTERPRISES/34RES/9584/1.5 Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find highest value in a specific data set.
That is a perfect use of a pivot table: Company in the row field, Group in the row field, Value in
the data field, set to MAX. - HTH, Bernie MS Excel MVP "dj479794" wrote in message ... Excel 2003 I need a formula that will tell me the largest number in the "Value" column for a specific "Group". In the sample data below the value for ACME's "USA" group would be "1.5" for their "UK" Group it would be "0.5" "DJ INDUSTRIES "Alpha" group it would be "1.0". Some "Group" Names maybe the same for diffferent companies ("Company"). So the formula needs to identify a change in "company" value as well. Example of Data: "/" = Denotes New Column of Data First Row are Column Headers Company / Group / Product Code / Value / {Column for formula} ACME/USA/3456/0.5 ACME/USA/3456/0.5 ACME/USA/5432/1.0 ACME/USA/9584/1.5 ACME/UK/3456/0.5 ACME/UK/3456/0.5 DJ INDUSTRIES/ALPHA/5432/1.0 DJ INDUSTRIES/ALPHA/3456/0.5 JR ENTERPRISES/34RES/9584/1.5 Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find highest value in a specific data set.
Thanks. The "max" setting is what I was missing.
"Bernie Deitrick" wrote: That is a perfect use of a pivot table: Company in the row field, Group in the row field, Value in the data field, set to MAX. - HTH, Bernie MS Excel MVP "dj479794" wrote in message ... Excel 2003 I need a formula that will tell me the largest number in the "Value" column for a specific "Group". In the sample data below the value for ACME's "USA" group would be "1.5" for their "UK" Group it would be "0.5" "DJ INDUSTRIES "Alpha" group it would be "1.0". Some "Group" Names maybe the same for diffferent companies ("Company"). So the formula needs to identify a change in "company" value as well. Example of Data: "/" = Denotes New Column of Data First Row are Column Headers Company / Group / Product Code / Value / {Column for formula} ACME/USA/3456/0.5 ACME/USA/3456/0.5 ACME/USA/5432/1.0 ACME/USA/9584/1.5 ACME/UK/3456/0.5 ACME/UK/3456/0.5 DJ INDUSTRIES/ALPHA/5432/1.0 DJ INDUSTRIES/ALPHA/3456/0.5 JR ENTERPRISES/34RES/9584/1.5 Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find highest value in a specific data set.
Hi,
This array worked for me as well, but I have another problem. I search for a value and finding MAX and MIN and so far so good but when my number is missing it is returning 0 and in my case this is no good answer because the answer can be 0 and know I don´t know if this is the max/min value or just a missing value. "Don Guillett" skrev: An ARRAY formula that must be entered using CSE( ctrl +shift+enter) =MAX(IF((A2:A22="Acme")*(B2:B22="usa"),D2:D22)) substitute range ref for acme, etc, if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "dj479794" wrote in message ... Excel 2003 I need a formula that will tell me the largest number in the "Value" column for a specific "Group". In the sample data below the value for ACME's "USA" group would be "1.5" for their "UK" Group it would be "0.5" "DJ INDUSTRIES "Alpha" group it would be "1.0". Some "Group" Names maybe the same for diffferent companies ("Company"). So the formula needs to identify a change in "company" value as well. Example of Data: "/" = Denotes New Column of Data First Row are Column Headers Company / Group / Product Code / Value / {Column for formula} ACME/USA/3456/0.5 ACME/USA/3456/0.5 ACME/USA/5432/1.0 ACME/USA/9584/1.5 ACME/UK/3456/0.5 ACME/UK/3456/0.5 DJ INDUSTRIES/ALPHA/5432/1.0 DJ INDUSTRIES/ALPHA/3456/0.5 JR ENTERPRISES/34RES/9584/1.5 Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find highest value in a specific data set.
??
-- Don Guillett Microsoft MVP Excel SalesAid Software "Anna" wrote in message ... Hi, This array worked for me as well, but I have another problem. I search for a value and finding MAX and MIN and so far so good but when my number is missing it is returning 0 and in my case this is no good answer because the answer can be 0 and know I don´t know if this is the max/min value or just a missing value. "Don Guillett" skrev: An ARRAY formula that must be entered using CSE( ctrl +shift+enter) =MAX(IF((A2:A22="Acme")*(B2:B22="usa"),D2:D22)) substitute range ref for acme, etc, if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "dj479794" wrote in message ... Excel 2003 I need a formula that will tell me the largest number in the "Value" column for a specific "Group". In the sample data below the value for ACME's "USA" group would be "1.5" for their "UK" Group it would be "0.5" "DJ INDUSTRIES "Alpha" group it would be "1.0". Some "Group" Names maybe the same for diffferent companies ("Company"). So the formula needs to identify a change in "company" value as well. Example of Data: "/" = Denotes New Column of Data First Row are Column Headers Company / Group / Product Code / Value / {Column for formula} ACME/USA/3456/0.5 ACME/USA/3456/0.5 ACME/USA/5432/1.0 ACME/USA/9584/1.5 ACME/UK/3456/0.5 ACME/UK/3456/0.5 DJ INDUSTRIES/ALPHA/5432/1.0 DJ INDUSTRIES/ALPHA/3456/0.5 JR ENTERPRISES/34RES/9584/1.5 Thanks! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find highest value in a specific data set.
Anna,
These will only look at actual (non-missing) values: =MAX(IF((A2:A22="Acme")*(B2:B22="usa")*(D2:D22<"" ),D2:D22)) =MIN(IF((A2:A22="Acme")*(B2:B22="usa")*(D2:D22<"" ),D2:D22)) HTH, Bernie MS Excel MVP "Anna" wrote in message ... Hi, This array worked for me as well, but I have another problem. I search for a value and finding MAX and MIN and so far so good but when my number is missing it is returning 0 and in my case this is no good answer because the answer can be 0 and know I don´t know if this is the max/min value or just a missing value. "Don Guillett" skrev: An ARRAY formula that must be entered using CSE( ctrl +shift+enter) =MAX(IF((A2:A22="Acme")*(B2:B22="usa"),D2:D22)) substitute range ref for acme, etc, if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "dj479794" wrote in message ... Excel 2003 I need a formula that will tell me the largest number in the "Value" column for a specific "Group". In the sample data below the value for ACME's "USA" group would be "1.5" for their "UK" Group it would be "0.5" "DJ INDUSTRIES "Alpha" group it would be "1.0". Some "Group" Names maybe the same for diffferent companies ("Company"). So the formula needs to identify a change in "company" value as well. Example of Data: "/" = Denotes New Column of Data First Row are Column Headers Company / Group / Product Code / Value / {Column for formula} ACME/USA/3456/0.5 ACME/USA/3456/0.5 ACME/USA/5432/1.0 ACME/USA/9584/1.5 ACME/UK/3456/0.5 ACME/UK/3456/0.5 DJ INDUSTRIES/ALPHA/5432/1.0 DJ INDUSTRIES/ALPHA/3456/0.5 JR ENTERPRISES/34RES/9584/1.5 Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I find 2nd highest MAX in a row | Excel Discussion (Misc queries) | |||
display specific next highest value from a column containing multi | Excel Discussion (Misc queries) | |||
HOw do i find and sort out data repeats for specific data set | Excel Discussion (Misc queries) | |||
How can I find and sort specific data within a column? | Excel Discussion (Misc queries) | |||
Find column letter containing specific data | Excel Worksheet Functions |