topic Re: Using "If" formula sometimes returns blank cells in Discussions

Using "If" formula sometimes returns blank cells

mrichie — Sat, 04 Nov 2017 01:19:50 GMT

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!

Re: Using "If" formula sometimes returns blank cells

ih — Sat, 04 Nov 2017 01:49:15 GMT

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:

Re: Using "If" formula sometimes returns blank cells

mrichie — Sat, 04 Nov 2017 02:21:18 GMT

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!

Re: Using "If" formula sometimes returns blank cells

Craige_Hales — Sun, 05 Nov 2017 23:31:59 GMT

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.