Re: [libreoffice-users] VLOOKUP Syntax to find MAX on a particular date

On 8 Aug 2022 at 21:25, Hylton Conacher (ZR1HPC) 
wrote:

Date sent:              Mon, 8 Aug 2022 21:25:44 +0200
To:                     LibreOffice Users 
<users@global.libreoffice.org>
From:                   "Hylton Conacher (ZR1HPC)" 
<hylton@conacher.co.za>
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: users+unsubscribe@global.libreoffice.org
> 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:mikes@guam.net                            
 mailto:msetzerii@gmail.com
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
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