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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Using "If" formula sometimes returns blank cells

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 3, 2017 6:19 PM
(1415 views)

I'm a student using JMP for my first time, so apologies for any wrong terminology.

I'm using this formula to assign a code to different spending ranges for RFM analysis.

The problem I'm encountering is with the top value 259.85. It sometimes returns the correct code "5" and other times returns a blank cell.

Can anybody help me clear this up? Thanks for any input!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 3, 2017 6:49 PM
(2817 views)

Solution

The value total_money could be between 259.84 and 259.85. You might try removing the upper bound of the 'else if' conditions in the if statement and leaving an open 'else' condition. Two other common problems that do not appear to apply here are missing data and the wrong type of data. Here is an example:

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 3, 2017 6:49 PM
(2818 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 3, 2017 7:21 PM
(1405 views)

Thanks, ih. Based on your input I reworked the formula to look like this:

I'm not sure if that's exactly what you intended, but it follows the spirit of your advice, at least, in removing room for values to hide between the upper bounds of one if statement and the lower bounds of the next.

It does seem odd to me, however, because I don't believe this data goes beyond 2 decimal points, so I don't understand how anything could fall between .84 and .85.

Thanks so much for the help!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 5, 2017 3:31 PM
(1371 views)

Here's a blog on **if**.

Here's a previous question about representing base-10 decimal numbers in binary floating point. The short answer is a number like .1 or .2 is represented in binary much like 1/3 is in base 10: an infinite repeating sequence of digits. It gets truncated at some point, and rounded, and might be slightly large or slightly small. (*slightly* is about 1e-15). In the same way that .333+.333 != .667, .1+.2 !=.3

```
.1+.2==.3
```

0 // *why not? subtract them and see why they are different*...

```
(.1 + .2) - .3
```

5.55111512312578e-17 // *that's a really small difference*

This is not specific to JMP; see the Excel comment in the second link above.

Depending how Total_money was calculated, the value near 259.85 may be 259.8500..01 or 259.8499..99 (roughly) and you might want your if statement to split the pennies at the half-way point (untested code, approximately right):

```
// caution: counting pennies using floating point will be painful!
if( total_money > 259.845, "5", // 259.85 and above
total_money > 139.895, "4", // 139.90 and above
total_money > 89.845, "3", // 89.85 and above
...
"0") // none of the above
```

The *if* does each test in order and uses the first true answer. The final "0" answer has no test and is the *else* clause of the if statement.

Craige