[Solved] Given a date and a range of dates, how find max

Discuss the spreadsheet application
Post Reply
shrimpwidget
Posts: 4
Joined: Sat Sep 18, 2021 3:18 am

[Solved] Given a date and a range of dates, how find max

Post by shrimpwidget »

Given a cell with a date (e.g. E16) and given a range of cells with dates (e.g. A5:A20), I desire a formula that tells me the (first occurrence of) maximum date found in that range that is less than or equal to the given date.

A cool next step after that would be to know which cell contained that max value, because I would love to grab a related value from that same row (I'm thinking OFFSET helps with finding the related value in the same row of the cell with max date).

I tried this and it fails:

Code: Select all

=MAX(IF($A$5:$A$20=E16;A5:A20))
Last edited by Hagar Delest on Sat Sep 18, 2021 9:25 am, edited 3 times in total.
Reason: Tagged [Solved].
OpenOffice 4.1 on Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Given a date and a range of dates, how find max < given

Post by FJCC »

Use

Code: Select all

=MAX((A5:A20<=E16)*A5:A20)
entered as an array formula.Enter the formula and finish with CTRL+Shift+Enter.
You can use VLOOKUP to find values in the same row.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Given a date and a range of dates, how find max < given

Post by RusselB »

Welcome to the Forums
I'm going to suggest the VLOOKUP function.
The formula, using the same parameters you have given would look like

Code: Select all

=VLOOKUP(E16;$A$5:$A$20;1)
Please note that the values in A5:A20 must be sorted from low to high.
If they are not, then this may fail.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
shrimpwidget
Posts: 4
Joined: Sat Sep 18, 2021 3:18 am

Re: Given a date and a range of dates, how find max < given

Post by shrimpwidget »

FJCC wrote:Use

Code: Select all

=MAX((A5:A20<=E16)*A5:A20)
This is not working for me. It generates 12/30/99 and not the correct answer (as does VLOOKUP in the next Commenter's answer).

Ah, I have the cell formatted to show a date, and I suspect that 12/30/99 is the date-formatted version of the value 0
Last edited by shrimpwidget on Sat Sep 18, 2021 4:20 am, edited 3 times in total.
OpenOffice 4.1 on Windows 7
shrimpwidget
Posts: 4
Joined: Sat Sep 18, 2021 3:18 am

Re: Given a date and a range of dates, how find max < given

Post by shrimpwidget »

RusselB wrote:Welcome to the Forums
Thanks!
RusselB wrote:

Code: Select all

=VLOOKUP(E16;$A$5:$A$20;1)
Please note that the values in A5:A20 must be sorted from low to high.
If they are not, then this may fail.
This works. Thankfully, my dates are trusted to be in sort order.
OpenOffice 4.1 on Windows 7
shrimpwidget
Posts: 4
Joined: Sat Sep 18, 2021 3:18 am

Re: [Solved] Given a date and a range of dates, how find max

Post by shrimpwidget »

Code: Select all

=MAX((A5:A20<=E16)*A5:A20)
This fails for me. Always shows "0". Even when I fill the cell with this equation and press ctrl-shift-Enter.

I wonder if it hints at something wrong in the data of A5:A20. I see no error. All cells seem to be formatted as Date. No idea... It had worked at one point.
OpenOffice 4.1 on Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Given a date and a range of dates, how find max

Post by FJCC »

Please upload a smal file showing the problem. To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Given a date and a range of dates, how find max

Post by RoryOF »

Try /View/Value highlighting to see the types of the data, shown by colour - text is black, numbers blue, results of formulae green.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Given a date and a range of dates, how find max

Post by Zizi64 »

The dates must be numeric values - if you want to make some numeric calculation/comparison with them. They can be formatted or not formatted numeric data.
Your dates probably just some date-like strings with zero numeric value.

Please upload your sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply