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. |
Complicated
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. |
Complicated
On Thu, 5 Jan 2006 14:10:02 -0800, "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. 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 |
Complicated
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" 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 |
All times are GMT +1. The time now is 09:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com