Date: prev next · Thread: first prev next last
2020 Archives by date, by thread · List index

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.

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
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:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.