Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 39
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 39
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 132
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I find 2nd highest MAX in a row cruise director Excel Discussion (Misc queries) 4 August 20th 08 12:16 AM
display specific next highest value from a column containing multi bvasquez Excel Discussion (Misc queries) 13 May 10th 08 09:38 PM
HOw do i find and sort out data repeats for specific data set JRDePaul Excel Discussion (Misc queries) 0 February 27th 07 12:55 AM
How can I find and sort specific data within a column? bpreas - ExcelForums.com Excel Discussion (Misc queries) 3 August 2nd 05 07:11 PM
Find column letter containing specific data markx Excel Worksheet Functions 4 March 17th 05 11:41 PM


All times are GMT +1. The time now is 10:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"