On 8 Aug 2022 at 21:25, Hylton Conacher (ZR1HPC)
wrote:
Date sent: Mon, 8 Aug 2022 21:25:44 +0200
To: LibreOffice Users
<[email protected]>
From: "Hylton Conacher (ZR1HPC)"
<[email protected]>
Subject: [libreoffice-users] VLOOKUP Syntax to find
MAX on a particular date
> Hi,
>
> Using LO Calc 7.3.2.2 and I have a 28 column table RAW Data table in
> excess of 65k rows. A three column sample below:
> Date Simple Date Outdoor Temp
> 2022-01-01T00:05:00+02:00 2022-01-01 00:05:00 17.5
> 2022-01-01T00:00:00+02:00 2022-01-01 00:00:00 17.4
>
> I have a secondary table, called Amended RAW Data containing just the 10
> digit date and the corresponding value of the column value I am seeking,
> whether it be the Outdoor Temp, Daily Rainfall etc
>
> What I need to do is query the following table, searching for a specific
> 10 digit date, and find the max value on a specific date.
>
> I have tried so many different itinerations on the VLOOKUP formula and
> browsed many elementary Google VLOOKUP pages that I need help. I have
> tried(all return N/A):
>
> =MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62037,10),$'RAW
> Data'.$B$2:$L$62037,1,FALSE))
>
> =MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11))
>
> =MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11))
>
> =MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62307,10)=$'Amended RAW
> Data'.$A$11,$'RAW Data'.$B$2:$L$62037,11,0))
>
> References to 'Amended RAW Data'.$A$11 refer to a date that I know has
> greater than zero value, and the 11th column data I want to max, within
> a date.
>
> Help appreciated
> Hylton
Not sure I understand you completely, but here is a test I
did using maxifs.
In cells A5-A8
Date
2022-01-01T00:05:00+02:00
2022-01-01T00:00:00+02:00
2022-01-02T00:00:00+02:00
In cells B5-B8
Simple Date
01/01/2022 12:05 AM
01/01/2022 12:00 AM
01/02/2022 12:00 AM
In cells C5-C8
Outdoor Temp
17.5
17.4
26
In cells E6-E7
01/01/22
01/02/22
In cells F6 and F7
=MAXIFS($C$6:$C$8,$B$6:$B$8,">="&$E6,$B$6:$B$8,"<"&($E6+1))
=MAXIFS($C$6:$C$8,$B$6:$B$8,">="&$E7,$B$6:$B$8,"<"&($E7+1))
Displayed values of 17.5 and 26
Using Maxifs to get the max of range with outdoor temp, but date needs to be >=
date in E
and less than that +1 (next day)..
>
> --
> To unsubscribe e-mail to: [email protected]
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
+------------------------------------------------------------+
Michael D. Setzer II - Computer Science Instructor
(Retired)
mailto:[email protected]
mailto:[email protected]
Guam - Where America's Day Begins
G4L Disk Imaging Project maintainer
http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+
--
To unsubscribe e-mail to: [email protected]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
