If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need ideas on which formula(s) to use in this situation
I have a worksheet with a lot of raw data. In one column I have an
identifier, in another I have dates, and in another column I have values. I need to build a summary table on a new worksheet that will (given a date and identifier) return the max value. I know MAX will get me the max value overall, but not limited to just my double match. In the past I've used things like sumproduct to pull multiple matches, but in this case I don't want all the values returned, just the max value. I don't need a polished formula, just some ideas on where to get started. Thanks! Keith ID Date Value --- ------ ------- A 1/4/06 12 B 1/4/06 8 A 1/4/06 11 D 1/4/06 7 G 1/5/06 14 F 1/1/06 12 A 1/5/06 17 D 1/3/06 8 B 1/4/06 5 V 1/3/06 9 so if I was checking A, 1/4/06 the result would be 12 |
#2
|
|||
|
|||
Need ideas on which formula(s) to use in this situation
ker_01 wrote:
I have a worksheet with a lot of raw data. In one column I have an identifier, in another I have dates, and in another column I have values. I need to build a summary table on a new worksheet that will (given a date and identifier) return the max value. I know MAX will get me the max value overall, but not limited to just my double match. In the past I've used things like sumproduct to pull multiple matches, but in this case I don't want all the values returned, just the max value. I don't need a polished formula, just some ideas on where to get started. Thanks! Keith ID Date Value --- ------ ------- A 1/4/06 12 B 1/4/06 8 A 1/4/06 11 D 1/4/06 7 G 1/5/06 14 F 1/1/06 12 A 1/5/06 17 D 1/3/06 8 B 1/4/06 5 V 1/3/06 9 so if I was checking A, 1/4/06 the result would be 12 You will likely get some formula options, but you might also want to consider a PivotTable. You can place "Date" in the Page Fields, "ID" in the Row Fields and "Value" in the Data Items (change from Sum to Max). |
#3
|
|||
|
|||
Need ideas on which formula(s) to use in this situation
=MAX(INDEX((A2:A11="A")*(B2:B11=--"1/4/2006")*C2:C11,))
"ker_01" wrote: I have a worksheet with a lot of raw data. In one column I have an identifier, in another I have dates, and in another column I have values. I need to build a summary table on a new worksheet that will (given a date and identifier) return the max value. I know MAX will get me the max value overall, but not limited to just my double match. In the past I've used things like sumproduct to pull multiple matches, but in this case I don't want all the values returned, just the max value. I don't need a polished formula, just some ideas on where to get started. Thanks! Keith ID Date Value --- ------ ------- A 1/4/06 12 B 1/4/06 8 A 1/4/06 11 D 1/4/06 7 G 1/5/06 14 F 1/1/06 12 A 1/5/06 17 D 1/3/06 8 B 1/4/06 5 V 1/3/06 9 so if I was checking A, 1/4/06 the result would be 12 |
#4
|
|||
|
|||
Need ideas on which formula(s) to use in this situation
=MAX(IF(A2:A20="A",C2:C20))
as an array formula, so commit with Ctrl-Shift-Enter, not just Enter. -- __________________________________ HTH Bob "ker_01" wrote in message ... I have a worksheet with a lot of raw data. In one column I have an identifier, in another I have dates, and in another column I have values. I need to build a summary table on a new worksheet that will (given a date and identifier) return the max value. I know MAX will get me the max value overall, but not limited to just my double match. In the past I've used things like sumproduct to pull multiple matches, but in this case I don't want all the values returned, just the max value. I don't need a polished formula, just some ideas on where to get started. Thanks! Keith ID Date Value --- ------ ------- A 1/4/06 12 B 1/4/06 8 A 1/4/06 11 D 1/4/06 7 G 1/5/06 14 F 1/1/06 12 A 1/5/06 17 D 1/3/06 8 B 1/4/06 5 V 1/3/06 9 so if I was checking A, 1/4/06 the result would be 12 |
Thread Tools | |
Display Modes | |
|
|