Re: [libreoffice-users] use calc 7 IF command to change some column values?

At 20:06 19/10/2020 -0700, Dave Stevens wrote:
> I have a column of 365 daily values, non-negative integers, (for a 
> calendar year). I want a column (column 2) just like that but with 
> some cells (start and end row specified) to be zeroed if they are too large.
>
> so...
> 1       1
> 3       3
> 21      21
> 29      0
> 22      22
> 0       0
>
> All values in column 1 to be copied to column 2 and if greater than 
> 28 replaced by zero.
>
> The help page gives a highly relevant IF function example that almost works.

Oh, it does; it does!

> I just don't see how to write the value of the column 1 contents 
> rather than the cell reference. The modified example is 
> '=IF(A1:A13>28,0,"too small")'. at first I tried changing "too 
> small" to A1 but I get "A1" in column 2 rather than a value. I don't 
> see the syntax to get column 1's value rather than its reference.

Just as in any other use of a spreadsheet, if you want the value of 
A1, you simply use A1 - without any quotes. If you are seeing the 
text A1 instead, you must be putting "A1" - with the quotes - in your 
formula. The example uses quotes around "too small" precisely because 
that is a literal piece of text and not a reference. Also, in the 
example, A1:A13 is a range of cells, but that is not what you need 
here. Instead, enter in B1 the formula
=IF(A1>28,0,A1)
and "fill" it down column B - by dragging the "fill handle" down the 
column to B365. The references to A1 change automatically to A2, A3, and so on.

I trust this helps.

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/
Privacy Policy: https://www.documentfoundation.org/privacy