Dave Stevens |
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: [hidden email] 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 |
Brian Barker |
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: [hidden email] 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 |
In reply to this post by Dave Stevens
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 <[hidden email]> 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: [hidden email] > 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 -- To unsubscribe e-mail to: [hidden email] 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 |
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 <[hidden email]> 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 <[hidden email]> 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: [hidden email] >> 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: [hidden email] 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 |
Free forum by Nabble | Edit this page |