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

On 10/08/2022 02:55, Johnny Rosenberg wrote:
> Den tis 9 aug. 2022 kl 15:50 skrev Hylton Conacher (ZR1HPC) <
> hylton@conacher.co.za>:
>
> > On 2022/08/09 14:53, Johnny Rosenberg wrote:
> > > Den tis 9 aug. 2022 kl 12:26 skrev Hylton Conacher (ZR1HPC)
> > > <hylton@conacher.co.za <mailto:hylton@conacher.co.za>>:
> > >
> > >      Hi Johnny,
> > >
> > >      On 2022/08/08 22:19, Johnny Rosenberg wrote:
> > >       > Den mån 8 aug. 2022 kl 21:47 skrev Hylton Conacher (ZR1HPC)
> > >       > <hylton@conacher.co.za <mailto:hylton@conacher.co.za>
> > >      <mailto:hylton@conacher.co.za <mailto:hylton@conacher.co.za>>>:
> > >       >
> > >       >     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
> > >       >
> > >       >
> > >       > Are those dates numbers formatted as dates or just text?
> > >
> > >      The Date column is formatted as number and the simplified Date is
> > >      formatted as text.
> > >
> > >      Is VLOOKUP fussy about the format of the cells it must search? I
> > >      thought
> > >      it is just matching a string of characters?
> > >
> > >
> > > It's not fussy about the format, but rather about the actual value in
> > > the cells, because if it's formatted as a date, it's really just a
> > > number (0.0 = 1899-12-30 00:00:00 and so on....
> > The tab called Raw Data is actually a .csv import of an external file on
> > which I have no ability to determine how the columns are formatted.
> How do you import your file?
> What would be an example of a complete line in that CSV file?
Further to Johnny's question, how is the CSV delimited. Do you just have 
commas between values or are some values also quoted in " ".
When I import a simple CSV using Sheet>Insert sheet from file I can set 
the simple date column as type date at import and using the MAXIFS 
solution proposed by Michael [ in my test 
=MAXIFS($C$2:$C$18,$B$2:$B$18,">="&G2,$B2:$B$18,"<"&(G2+1)) ] works well 
in a test.
My understanding is that VLOOKUP only returns 1 value, rather than a 
vector from which to determine a date.
Steve
> Kind regards
>
> Johnny Rosenberg
>
>
>
> > As time progresses so I will download another .csv file and overwrite
> > the existing one with an updated file.
> >
> > The bits that contain any formula will remain unchanged, apart from
> > having their ranges extended due to the additional rows added.
> >
> > > But I wonder about the other column that is formatted as text. What do
> > > you mean by that?
> > I clicked on one of the cells in the Simple Date column and selected
> > 'Format->Cell' and it returned that is how it was formatted.
> >
> > Tnx
> > Hylton
> >
> > --
> > 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


--
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