Complicated

I'm sorry if it's hard to understand what I'm asking about, but here it goes...

I have a spreadsheet organized by reference numbers, and each reference
number has multiple rows that contain data. I have been trying to figure out
a function that will locate the largest value of a certain piece of data for
each reference number.

Here is a generic example of the data...

Ref# data
12vt 2500
12vt 5400
12vt 1500
13vt 200
13vt 145
13vt 300

I need the function to find 5400 as the highest value for 12vt and 300 as
the highest value for 13vt.

With E2 housing a condition value like 12vt...

=MAX(IF($A$2:$A$7=E2,$B$2:$B$7))

which needs to be confirmed with control+shift+enter, not just with enter.

Brett wrote:
> I'm sorry if it's hard to understand what I'm asking about, but here it goes...
>
> I have a spreadsheet organized by reference numbers, and each reference
> number has multiple rows that contain data. I have been trying to figure out
> a function that will locate the largest value of a certain piece of data for
> each reference number.
>
> Here is a generic example of the data...
>
> Ref# data
> 12vt 2500
> 12vt 5400
> 12vt 1500
> 13vt 200
> 13vt 145
> 13vt 300
>
> I need the function to find 5400 as the highest value for 12vt and 300 as
> the highest value for 13vt.
>
>

On Thu, 5 Jan 2006 14:10:02 -0800, "Brett" <[email protected]>
wrote:

>I'm sorry if it's hard to understand what I'm asking about, but here it goes...
>
>I have a spreadsheet organized by reference numbers, and each reference
>number has multiple rows that contain data. I have been trying to figure out
>a function that will locate the largest value of a certain piece of data for
>each reference number.
>
>Here is a generic example of the data...
>
>Ref# data
>12vt 2500
>12vt 5400
>12vt 1500
>13vt 200
>13vt 145
>13vt 300
>
>I need the function to find 5400 as the highest value for 12vt and 300 as
>the highest value for 13vt.
>

One approach would be to use a pivot table.

Select a single cell in your table.

Data/Pivot Table
You can probably just use the default selections and go directly to Finish.
This will open up a new worksheet.

Drag Ref# to the rows area.
Drag Data to the data area.

Select some cell in the data area, and right-click on it.
Field Settings (from the r-click menu) and change Sum to Max

Check out the possible formatting of the table.


--ron

The Pivot Table solution is something I tried, but due to other complications
in the spreadsheet it doesn't work out right (not worth getting into). The
formula from Aladin provided, however, worked great. Here's my next
question...I need to use this function several hundred times, so is there a
way to automatically implement the ctrl+shift+enter all at once? Excel won't
let me copy and paste the formula into multiple rows, and when I try to
extend in down, I just get the original value and have to manually
ctrl+shift+enter to update.

Thanks!

"Ron Rosenfeld" wrote:

> On Thu, 5 Jan 2006 14:10:02 -0800, "Brett" <[email protected]>
> wrote:
>
> >I'm sorry if it's hard to understand what I'm asking about, but here it goes...
> >
> >I have a spreadsheet organized by reference numbers, and each reference
> >number has multiple rows that contain data. I have been trying to figure out
> >a function that will locate the largest value of a certain piece of data for
> >each reference number.
> >
> >Here is a generic example of the data...
> >
> >Ref# data
> >12vt 2500
> >12vt 5400
> >12vt 1500
> >13vt 200
> >13vt 145
> >13vt 300
> >
> >I need the function to find 5400 as the highest value for 12vt and 300 as
> >the highest value for 13vt.
> >
>
> One approach would be to use a pivot table.
>
> Select a single cell in your table.
>
> Data/Pivot Table
> You can probably just use the default selections and go directly to Finish.
> This will open up a new worksheet.
>
> Drag Ref# to the rows area.
> Drag Data to the data area.
>
> Select some cell in the data area, and right-click on it.
> Field Settings (from the r-click menu) and change Sum to Max
>
> Check out the possible formatting of the table.
>
>
> --ron
>