Need ideas on which formula(s) to use in this situation

  • Thread starter Thread starter ker_01
  • Start date Start date
K

ker_01

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
 
ker_01 said:
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
Click to expand...


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).
 
=MAX(IF(A2:A20="A",C2:C20))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter.
 
Back
Top