Estimating Visual Acuity with Formula

[Faronnj]

Another quick question related to this data:

So I have a bunch of null values in my preop VA sc column, I am not as good with writing the null formulas yet to achieve my goal.  This is a sample data:

Last	Preop IOL Ref Sph	Preop VA sc
Patient 1	-9.50
Patient 2	-15.50
Patient 2	-15.50
Patient 3	-20.25	2/500
Patient 4	-15.00
Patient 5	-7.00	20/100
Patient 5	-5.00	20/150
Patient 6	-14.00
Patient 6	-15.00

So in the preop VA sc column, there are null values and I want to estimate using a formula and replace the null values with the calculation decimal. 

The formula from excel: =(0.314*(preop IOL ref sph)^-1.368)

I would like alteryx to ideally, go down the list, find all the nulls and run the formula for those with null values which will give the snellen decimal visual acuity - which can then be converted to LogMAR using our previous discussion.  

Lastly: in this column for kids who cant speak for visual acuity, we use things like F, FF, FFM (not shown in the sample data).  Is there a way or formula to use to replace these "F" with the correct estimated value such as ".2" ?

I have a meeting with one of your healthcare engineers tomorrow morning and wanted to get this part of my data cleaned up to do more powerful analytics.  Thank you in advance,

Nick 

[echuong1 · Accepted solution]

You can use a conditional statement to achieve this. 

For example: 

IF isnull([Preop VA sc])
THEN [insert formula for estimation here]

ELSEIF [Preop IOL Ref Sph] in ('F', 'FF', 'FFM')
THEN .2
ELSE [Preop VA sc]
ENDIF

[Faronnj]

its saying unknown variable when using the following start:

if IsNull([Preop VA sc]) then [(0.314*[Preop IOL Ref Sph]^-1.368)]  endif

I just was doing the first part of the conditional and I think it is something with how I am formatting the equation.  Is there a better way to write the formula for estimation to remove the unknow variable "(0.314[preop IOL ref sph"

[echuong1]

The syntax for the power function is different in Alteryx. The function is POW([number], [exponent]). 

Also, is this the correct formula to be using? I tried using it in Excel and I'm getting errors...

[Faronnj]

This would be the example in excel with no IF commands, I never did IF commands in excel I would manually go through and do this calculation using the refractive error but i think your error is its the absolute value (needs to be positive) refractive sphere.  I tried adjusting my formula but still getting unknown variable for some reason even with inserting an absolute value of the preop IOL ref sph. 

Am I missing something in my formula to replace the nulls and do the calculation? 

Thanks for all your help, I greatly appreciate it. 

Nick 

[echuong1 · Accepted solution]

Making the refractive sphere value the absolute value works! 

The formula syntax would be something like below:

Note that values are shown in scientific notation because I set the datatype to be a string. You cannot mix datatypes in a column (have the calculated values be numbers and the other values shown in the Preop VA sc field be strings). You can theoretically combine the logic needed to turn these values into fractions within the same formula.

See attached for the workflow.