Find minimum value greater than a particular value

  • Thread starter Thread starter John Michl
  • Start date Start date
J

John Michl

If seen examples of how to do this but I'm drawing a blank.

Consider a row that has 12 columns each of which contain a date value.
Some of the dates are past, some in the future.

I two formulas: One to determine the closest future date and one to
determine the closest past date.

Can I do this with sumproduct some how?

Thanks for the help.

- John
 
Try these array formulas*:

For date values in A1:A20

Closest date greater than today
B1: =MIN(IF(A1:A20>TODAY(),A1:A20))

Closest date less than today
C1: =MAX(IF(A1:A20<=TODAY(),A1:A20))

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Ron,
That works like a charm. I dabled with both array and sumproduct
formulas but quickly created something more complex than your
relatively simple solution. Thanks a ton.

- John
 
You're very welcome, John...I'm glad you could use those formulas.

***********
Regards,
Ron

XL2002, WinXP-Pro
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function to Return a Date Value Q 4
Scrabble Value calculation for Welsh words 0
vlookup and match first value greater than 4
Monday closest to today's date? 6
Minimum Date Greater than Today() 6
Excel Macro for deleting Rows containing dates greater than "today" 0
Find minimum value in column. 3
greater of two values 1

Back
Top