topic Re: Column/value type issue with formula in Discussions

Column/value type issue with formula

patriciafm811 — Fri, 08 Nov 2024 12:51:30 GMT

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.

Re: Column/value type issue with formula

mmarchandFSLR — Fri, 08 Nov 2024 12:57:32 GMT

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

Re: Column/value type issue with formula

mmarchandFSLR — Fri, 08 Nov 2024 13:01:23 GMT

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

patriciafm811 — Fri, 08 Nov 2024 14:08:47 GMT

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

mmarchandFSLR — Fri, 08 Nov 2024 15:52:08 GMT

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"
)

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.