2018 Archives by date, by thread · List index

# Re: [libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF?

```At 18:04 27/04/2018 +0200, Hylton Conacher wrote:
```
I am running LibreOffice 5.4.6.2 and am using a spreadsheet to enable me to draw graphs based on rainfall received per year, up until the date prior to today. The figures also allow me to compare current rainfall to date to the same period in a previous year. I agree I should probably use Base but I do not think it would have solved the issue.
```
**Example Data***
,,,,,
Month,Day,M/D,2016,2017,2018,Average('16->'17)
Apr,1.04/01,3,0,1,1.5
Apr,2,04/02,0,8,0,4
,,,,,,
,,,,,,
,,,,,,
Apr,26,04/26,0,0,20,0

```
Assuming today is Apr 2nd, a formula to count how many days of rain there were in the current month would be =COUNTIF(\$F\$2:\$F2,">0").
```
Surely not? Rather something like =COUNTIF(\$F\$94:\$F94,">0").

```
The Total amount of rain that fell on that number of days, being 1, was 1mm, ...
```
If we are talking 2018, that seems to be zero in your data.

```
```... or taking it further 60% of the average rainfall to date for the month.
```
```
```
I don't know what fraction zero is of zero - as I'm not allowed to divide by zero. Does your mathematics allow this?
```
```
Assuming today is Apr 27, a formula to count how many days of rain there were in the current month is unknown, ...
```
```
Surely not? The current month (I think it's still 2018, unless some catastrophe has happened without my knowing) has apparently had six rainy days in your data.
```
```
... especially as there are undoubtedly values in the other un-shown 81 cells i.e. 3yrs and 27 remaining days?
```
```
Where are these "unshown" cells - somewhere else, and not in this spreadsheet at all? And how can the "current month" - surely April 2018 - contain over three years' worth of days?
```
```
```Basically take the starting cell reference \$F\$2 ...
```
```
Perhaps \$F\$3?

```
... and add the number of events that were ">0" to get the end reference for the COUNTIF formula.
```
Now you've lost me ...

```
=COUNTIF(\$F\$2:Add number of items ">0" to the starting line reference 2, to get the end of the COUNTIF range) i.e. being Apr 27, whilst there may be 26 days between the beginning of Apr and today i.e. Today()-1, there are only two instances in the above table where ">0" for a particular year, ...
```
```
Are you working with the current month - one month? Or with all the Aprils in three years - three non-contiguous months? Or with the period from 1 January 2016 up to yesterday? Or what?
```
```
```... thus making 2 the number of days it rained, ...
```
```

```
```... to receive the SUM of the 2018 rainfall being 21mm
```
```
Er, 67 mm?

```
Incidentally, you have the value 119 in L2 to indicate yesterday's row number, but this has evidently been added manually and also must be updated so. It should be fairly easy to establish this automatically, and even to embed any such calculation in any formula.
```
```
Brian Barker
```
--
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/
All messages sent to this list will be publicly archived and cannot be deleted
```