810117_en-US

liliconcom

Column/value type issue with formula

I am trying to get some data that we currently store in Excel into JMP. In Excel, we use a VLOOKUP function (the 'HIV alert limits' column correlated to the 'semester donations estimate' column) and then calculate the '% of standard' column (HIV total/HIV alert limits) to get a percent. 

 

 

In JMP, I have made the 'HIV alert limits' column with an IF formula. This formula works on a range and based on the 'semester donations estimate' column, we get the 'HIV alert limits' (so within each range gives us an allowed count). When I do this formula, it outputs it as a Character-Nominal value. Which would be fine if that was all I needed. But then I need to take that and get my '% of standard' column (HIV total column/HIV alert limits column). JMP does not like this and keeps giving me errors because of the Character-Nominal value. 

 

How can I fix this so that I can do both the 'HIV alert limits' and '% of HIV std' columns?

 

I am attaching a demo version with fake numbers, but all the formulas in it are correct. There is not formula currently in the '% of HIV std' column because of this error. 

Automation and ScriptingData Exploration and VisualizationWindowsRe: Column/value type issue with formula

The formula is quoting the returned digits:  it returns "1" instead of 1.  I would change the formula from 

 

If(
	0 <= :Semester donations estimate <= 10699, "1",
	10700 <= :Semester donations estimate <= 35199, "2",
	35200 <= :Semester donations estimate <= 70599, "3",
	70600 <= :Semester donations estimate <= 113899, "4",
	113900 <= :Semester donations estimate <= 162999, "5"
)

to

 

 

If(
	0 <= :Semester donations estimate <= 10699, 1,
	10700 <= :Semester donations estimate <= 35199, 2,
	35200 <= :Semester donations estimate <= 70599, 3,
	70600 <= :Semester donations estimate <= 113899, 4,
	113900 <= :Semester donations estimate <= 162999, 5
)

 

and have a numeric column.

 

Re: Column/value type issue with formula

I don't know why :HIV Alert Limits gives. character data. How do you change it to return numeric values?

 

BUT the Num(alert limits) works!! Thank you!!

Re: Column/value type issue with formula

Either change the formula in :HIV Alert Limits to return numeric values, or convert the values inside the :"% of HIV std"n formula:

 

 

Re: Column/value type issue with formula

Why does :HIV Alert Limits give character data?  Why isn't it coded to return numeric?