Formula help please

[DrH100]

In a spreadsheet I have a column of dates (column r).

I have a formula in a cell

=INDEX($R$4:$R$57, MATCH(MIN(ABS($R$4:$R$57-$H$7)), ABS($R$4:$R$57-$H$7), 0))

Which is looking for the nearest date in column r (my list) to the date in H7. This has been working fine but I now need to tweak it a bit to look for the nearest date, but not past the date in H7.

I'm not sure which bit of this formula I should tweak - or if indeed it can be tweaked to do this.

I thought about something like

=INDEX(R4:R57, MATCH(MIN(IF(R4:R57-$H$7<=0, R4:R57-$H$7, "")), R4:R57-$H$7, 0))

but this just returns the first entry in R4 everytime no mater what the date in h7

As always any help appreciated.

 

[RickXL]

I have not actually tried this but don't you just need to lose the two ABS functions. They are what is causing a date close to H7 to count no matter which side it is on.

 

[DrH100]

Thanks RickXL

I must admit I tried taking the ABS functions out but that just takes it to the first entry in the list no matter what date is put in my cell H7.

Thanks for the reply though.

David

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX($R$4:$R$57, MATCH(MIN(IF($R$4:$R$57>$H$7,9.99999999999999E+307,ABS($R$4:$R$57-$H$7))), 
   ABS($R$4:$R$57-$H$7), 0))

 

[Marcelo Branco]

Maybe something like this...

=MAX(IF(H7>=R4:R57,R4:R57))

Ctrl+Shift+Enter, not just Enter

M.

 

[RickXL]

It's a good puzzle, David

This seems to work:
=$H$7+MAX(IF(($R$4:$R$57-$H$7)<=0,($R$4:$R$57-$H$7),-999999))

and this:
=$H$7+MAX(IF(($R$4:$R$57-$H$7)<=0,($R$4:$R$57-$H$7)))

So does this:
=INDEX(SMALL($R$4:$R$57,ROW(R$4:$R$57)-3),MATCH($H$7,SMALL($R$4:$R$57,ROW($R$4:$R$57)-3),1))

Both need Ctrl + Shift + Enter.

The problem becomes easier if the list of dates is in order.

 

[Marcelo Branco]

It's a good puzzle, David

This seems to work:
=$H$7+MAX(IF(($R$4:$R$57-$H$7)<=0,($R$4:$R$57-$H$7),-999999))

and this:
=$H$7+MAX(IF(($R$4:$R$57-$H$7)<=0,($R$4:$R$57-$H$7)))

So does this:
=INDEX(SMALL($R$4:$R$57,ROW(R$4:$R$57)-3),MATCH($H$7,SMALL($R$4:$R$57,ROW($R$4:$R$57)-3),1))

Both need Ctrl + Shift + Enter.

The problem becomes easier if the list of dates is in order.

I think my formula in #8 is simpler

=MAX(IF(H7>=R4:R57,R4:R57))

Ctrl+Shift+Enter

M.

 

[Aladin Akyurek]

I interpret(ed) the following differently:

"now need to tweak it a bit to look for the nearest date, but not past the date in H7."

http://www.mrexcel.com/forum/excel-questions/770098-formula-help-please.html#post3774472

 

[RickXL]

Marcelo,

I tried your formula and it didn't work.

Now I have rebooted my brain and go my act into gear I see that it does, in fact work very well.

I salute your conciseness

Rick

 

[Marcelo Branco]

Marcelo,

I tried your formula and it didn't work.

Now I have rebooted my brain and go my act into gear I see that it does, in fact work very well.

I salute your conciseness

Rick

Rick,

Thank you for your kind words.



M.