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

2022-08-10 Thread Steve Edmonds 

Hi Hylton

On 10/08/2022 19:28, Hylton Conacher (ZR1HPC) wrote:

Hi Steve, et al,

On 2022/08/09 23:05, Steve Edmonds wrote:



...
Further to Johnny's question, how is the CSV delimited. Do you just 
have commas between values or are some values also quoted in " ".

There are no " "'s, all comma separated.

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,">=",$B2:$B$18,"<"&(G2+1)) ] works 
well in a test.


Steve, As a courtesy I have sent you a month's worth of data in a csv 
file downloaded from my Ambient Weather station cloud account. Try it, 
as by no means is it a simple one especially when all you want is the 
rainfall on each of the days of the month. into Base
My understanding is that VLOOKUP only returns 1 value, rather than a 
vector from which to determine a date.


I believe what I need to accomplish cannot be done with VLOOKUP and 
that a combination of INDEX(MATCH()) is a better solution especially 
given that the RAW Data file import is going to grow approximately 1MB 
per month, despite being just .csv type.


Moving on from this means that the whole file should preferably be 
manipulated in Base.
Great idea. I currently do a similar thing loading the data csv file 
direct into MySQL every day with a Perl script and then display my 
queries and analysis in web pages for our employees using PHP. For me 
this is much simpler and more flexible and than Calc or Base, especially 
in a multi-user environment


Sorry for wasting everyone's time, Base questions coming soon 0:)

Hylton




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

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

2022-08-10 Thread Hylton Conacher (ZR1HPC) 

Hi Steve, et al,

On 2022/08/09 23:05, Steve Edmonds wrote:



...
Further to Johnny's question, how is the CSV delimited. Do you just have 
commas between values or are some values also quoted in " ".

There are no " "'s, all comma separated.

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,">=",$B2:$B$18,"<"&(G2+1)) ] works well 
in a test.


Steve, As a courtesy I have sent you a month's worth of data in a csv 
file downloaded from my Ambient Weather station cloud account. Try it, 
as by no means is it a simple one especially when all you want is the 
rainfall on each of the days of the month. into Base
My understanding is that VLOOKUP only returns 1 value, rather than a 
vector from which to determine a date.


I believe what I need to accomplish cannot be done with VLOOKUP and that 
a combination of INDEX(MATCH()) is a better solution especially given 
that the RAW Data file import is going to grow approximately 1MB per 
month, despite being just .csv type.


Moving on from this means that the whole file should preferably be 
manipulated in Base.


Sorry for wasting everyone's time, Base questions coming soon 0:)

Hylton

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

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

2022-08-09 Thread Steve Edmonds 



On 10/08/2022 02:55, Johnny Rosenberg wrote:

Den tis 9 aug. 2022 kl 15:50 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:


On 2022/08/09 14:53, Johnny Rosenberg wrote:


Den tis 9 aug. 2022 kl 12:26 skrev Hylton Conacher (ZR1HPC)
mailto:hyl...@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)
  > mailto:hyl...@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:
  > DateSimple 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,">=",$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+unsubscr...@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+unsubscr...@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

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

2022-08-09 Thread Johnny Rosenberg 
Den tis 9 aug. 2022 kl 15:50 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:

> On 2022/08/09 14:53, Johnny Rosenberg wrote:
> >
> >
> > Den tis 9 aug. 2022 kl 12:26 skrev Hylton Conacher (ZR1HPC)
> > mailto:hyl...@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)
> >  > mailto:hyl...@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:
> >  > DateSimple 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?


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+unsubscr...@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+unsubscr...@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

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

2022-08-09 Thread Hylton Conacher (ZR1HPC) 

On 2022/08/09 14:53, Johnny Rosenberg wrote:



Den tis 9 aug. 2022 kl 12:26 skrev Hylton Conacher (ZR1HPC) 
mailto:hyl...@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)
 > mailto:hyl...@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.
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+unsubscr...@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

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

2022-08-09 Thread Hylton Conacher (ZR1HPC) 

On 2022/08/08 23:55, Michael H wrote:
1. Vlookup can only return columns to the left of the column you search 
so you need to introduce the date into a leftward column (N2=B2, copy 
it, paste into column N, all rows)


All the examples I see on the net show that the left most column is the 
one used as the Search Criteria?


In my formulae all state the range from column B, being the left most 
column and include the columns to the right as the one who data I am 
seeking when a match is made in the left most column.



2. =vlookup(Max($rawdata.$B2:$M$62037),$rawdata.$b$2:$N63037,12,0)

SO NOW I HAVE A DATE IN THE LEFT MOST AND RIGHT MOST COLUMN?

??

Hylton

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

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

2022-08-09 Thread Hylton Conacher (ZR1HPC) 

Hi Johnny,

On 2022/08/08 22:19, Johnny Rosenberg wrote:
Den mån 8 aug. 2022 kl 21:47 skrev Hylton Conacher (ZR1HPC) 
mailto:hyl...@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?


Tried formatting column B in Raw Data as both Number and Text, with no 
change in the formulae answers.


Hylton

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

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

2022-08-08 Thread Rob Jasper 
For the following I assumed the “simple date” column is is date format..
If not you can get to that by entering the formula 
=IFERROR(DATEVALUE(LEFT($RAW.A3;10));"") and pull that down.

On another sheet I entered the “query date” in B2

The next formula gives you the max temp on that date:
=MAXIFS($’RAW data'.$C$2:$C$16;$’RAW data'.B$2:B$16;"="&$B$1)

Hope this helps,
Rob


> Op 8 aug. 2022, om 21:25 heeft Hylton Conacher (ZR1HPC) 
>  het volgende geschreven:
> 
> 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
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@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+unsubscr...@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

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

2022-08-08 Thread Michael D. Setzer II 
On 8 Aug 2022 at 21:25, Hylton Conacher (ZR1HPC) 
wrote:

Date sent:  Mon, 8 Aug 2022 21:25:44 +0200
To: LibreOffice Users 

From:   "Hylton Conacher (ZR1HPC)" 

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+unsubscr...@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:mi...@guam.net
 mailto:msetze...@gmail.com
 Guam - Where America's Day Begins
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
++




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

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

2022-08-08 Thread Hylton Conacher (ZR1HPC) 

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:

DateSimple 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

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