Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated Stacked Bar Chart? | Charts and Charting in Excel | |||
Complicated sort function with sort and sum | Excel Worksheet Functions | |||
Complicated Formulas | Excel Worksheet Functions | |||
Complicated Formula | Excel Discussion (Misc queries) | |||
How do you create a complicated pivot table graph? | Charts and Charting in Excel |