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

a correction to my message

This statement
puts the A column value in the corresponding row in column B if that value
is less than 28, otherwise it puts 0 in the column B row.

Should instead read as follows
puts the A column value in the corresponding row in column B if that value
is less than 29, otherwise it puts 0 in the column B row.

or, to state it the way the formula is written
puts 0 in the corresponding row in column B if the value in column A is
greater than 28, otherwise it puts the A row value in the corresponding B
row.

On Tue, Oct 20, 2020 at 7:03 AM Alan B <aboba0@gmail.com> wrote:

> Dave,
>
> Not sure I'm following your intention but...
>
> This formula:
> {=IF(A1:A13>28,0,A1:A13)}
>
> puts the A column value in the corresponding row in column B if that value
> is less than 28, otherwise it puts 0 in the column B row.
>
> Note the curly braces surrounding the formula. These cannot be typed in.
> The formula will not work if they are. To enter this formula, type it in
> without the curly braces and then use Ctrl+Shift+Enter to complete the
> formula.
>
> Ctrl+Shift+Enter are the magic keys to create an array formula. Once
> formula entry is completed with those keys you will see the formula has the
> curly braces, values in A*n* will be in B*n* if they are less than 28,
> otherwise 0 will appear in B*n*.
>
> On Mon, Oct 19, 2020 at 11:07 PM Dave Stevens <geek@uniserve.com> wrote:
>
> > I have a column of 365 daily values, non-negative integers, (for a
> > calendar year)
> >
> > I want a columns (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. 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.
> >
> > Anyone?
> >
> > Dave
> >
> >
> > --
> > 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
>
> --
> Alan Boba
> CISSP, CCENT, ITIL v3 Foundations 2011

-- 
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011

-- 
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