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

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