PDA

View Full Version : Solved: Formula needed to match dates with MIN & MAX values



geedee65
08-21-2007, 04:49 AM
I have a worksheet that contains a date in column B and a value in column C.
Cell "C2" contains a MAX formula & cell "C3" a MIN formula for all the values below them in column C.

What I would like to do is to put a formula in "B2" & "B3" that returns the corresponding date on which those values occur.
If there is more than one date that a MAX or MIN occurs, then to return the latest date.

Much appreciate any help.

p45cal
08-21-2007, 05:17 AM
Are the dates sorted? If so, most recent at the top or most recent at the bottom? (I'm looking at using Match, and want to know if the first match will return the right date).
p45cal

geedee65
08-21-2007, 05:31 AM
The most recent date is at the bottom.
The operator puts in a value each day and the monthly report requires the date on which the min & max value occured.

Bob Phillips
08-21-2007, 06:02 AM
=MAX(IF($C$4:$C$15=C2,$B$4:$B$15))

=MIN(IF($C$4:$C$15=C3,$B$4:$B$15))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

geedee65
08-21-2007, 06:19 AM
Thanks xld......
the max formula works, but the min formula is returning the earliest date, not the latest date?

Bob Phillips
08-21-2007, 06:20 AM
Sorry, my Faux-pas. They should both be MAX then.

geedee65
08-21-2007, 06:26 AM
thanks .... works perfectly.