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!

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using "If" formula sometimes returns blank cells

mrichie

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.
If Formula.png

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!

Accepted answers

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:

conditional.PNG

All comments

conditional.PNG

mrichie

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

If Formula 2.png

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!

Craige_Hales

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.