Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Why can't this formula do the whole column calculation?

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 19, 2018 7:01 AM
(4602 views)

The requirement is that if the number of rows is less than 25 and the rightmost number in the first column is 0, extract the 12 Numbers in the first column, otherwise return 0

If I write the formula this way, it will appear that when the rightmost number in the first column is 1, there will be no result, not even the calculation below.

Thanks for your guidance!

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

If( Row() < 25 & Substr( :Label, 25, 1 ) == "0",

Substr( :Label, 14, 12 ),

0

)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Oct 19, 2018 7:11 AM
| Last Modified: Oct 19, 2018 7:12 AM
(4595 views)
| Posted in reply to message from lwx228 10-19-2018

There may be some other issues, but the first thing is that your formula is mixing data types.

The Label column is character. Your formula in the "if" clause is using a substring, which is a character function. That is good.

Your "else" clause just has a zero (0), which is numeric. Put that in double quotes (like you did in the "if" statement) to get rid of the JMP alert that you captured.

Alternatively, you could put a Num() around the SubStr(Label,14,12) and make sure Column3 is numeric.

Dan Obermiller

3 REPLIES 3

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

If( Row() < 25 & Substr( :Label, 25, 1 ) == "0",

Substr( :Label, 14, 12 ),

0

)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Oct 19, 2018 7:11 AM
| Last Modified: Oct 19, 2018 7:12 AM
(4596 views)
| Posted in reply to message from lwx228 10-19-2018

There may be some other issues, but the first thing is that your formula is mixing data types.

The Label column is character. Your formula in the "if" clause is using a substring, which is a character function. That is good.

Your "else" clause just has a zero (0), which is numeric. Put that in double quotes (like you did in the "if" statement) to get rid of the JMP alert that you captured.

Alternatively, you could put a Num() around the SubStr(Label,14,12) and make sure Column3 is numeric.

Dan Obermiller

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Why can't this formula do the whole column calculation?

Thank you!

OK

If( Row() < 25 & Substr( :Label, 25, 1 ) == "0",

Substr( :Label, 14, 12 ),

"0"

)

OK

If( Row() < 25 & Substr( :Label, 25, 1 ) == "0",

Substr( :Label, 14, 12 ),

"0"

)

Article Labels

There are no labels assigned to this post.