Formula not producing correct result and pivot table empty

[brightside]

Hi helpful forum folks,

I am banging my head against the wall trying to get a simple calculation to produce the correct result and pivot table to show results!

The calc file can be viewed here. The columns on the left side of the first sheet are raw weather data copied and pasted as unformatted text from a website. The columns on the right side of the first sheet strip the non-numerical characters from the raw data, and format them as numbers. The very last column on the first sheet is a simple “if” statement to calculate a count of “rainy days”: if the value in the cell to the left is >0.1, then populate with a 1; otherwise, populate with a 0. You’ll see that the cells are populated with 1s despite the value of the cell to the left being zero! If I manually type a 0 into the cell, the if statement produces the correct result.

The pivot table on the second sheet is also not producing any results - except for the incorrect rainy day count!

I’m sure these two issues are linked. I’ve spent a few hours going through forum posts, playing with formatting, etc - no luck.

Thank you very much in advance!

[PKG]

Consequently, you have to convert the text into a number and you do this with the value function.

e.g.: =VALUE(LEFT(B3,LEN(B3)-2))

weather calc_PKG.ods (42,5 KB)

[brightside]

THANK YOU. Lesson for me here is to dive into the documentation of each command I’m using if I get stuck like this in the future.

It would appear that simply formatting a cell containing a text string as a “number” does not actually make that text string a number. This is good learning for me!

[sokol92]

We all start using spreadsheets (Calc, Excel, …) without even having a basic education.
The value of a cell (not a formula) on a Calc sheet can be of the following types:

• empty
• string
• number (double)
• error

(In Excel, the logical type is added to this list)
Changing the format of a cell never changes its value.