Convert Visual Acuity Snellen to LogMAR

New Day, New Question.

This one is extremely specific to ophthalmology research, I have an excel spreadsheet that has visual acuities in snellen form (20/20, 20/40, 20/60, etc.) I need to convert this to a logMAR scale, I have an about how this might be possible which ill try to relay and if you could help me do it in alteryx it would be greatly appreciated. Here is a condensed example of the data:

Preop VA sc	Preop VA cc	BCVA
20/1385	20/100
20/2700	20/30	20/30
20/2700	20/40	20/30
2/500		2/500
20/2600	20/100
20/250
20/250
20/2400	20/100
20/2650	20/100
20/1500	20/100	20/50
20/1050	20/40	20/40
20/900	20/30	20/30
20/2650	20/60	20/60
20/2100	20/60	20/60
20/3500	20/50
20/3800	20/60
10/500		10/400
20/1550	1/800	1/800
20/1650	20/160	20/80
20/250	20/125
20/1900	20/70	20/50
20/1800	20/60	20/50
20/2150	20/100	20/80

So the way I have done this in the past is going to a website that auto converts this for you (http://www.myvisiontest.com/) or using tables like such to manually convert.

My idea is to convert these fractions in excel to decimal form (not sure how I go about doing this since some of the fractions might not be in a numeric form), then using the formula [LogMAR = -log(snellen decimal)] convert all those decimals into logMAR scale in another column.

I need to do this so I can create a graph showing the preop and postop results which I should be able to figure out once I figure out how to convert these all to decimals and use the formula to convert them all to logmar scale. Not sure if this requires a multi-step or multi-formula and sort of lost where to start.

Very much appreciated in advance and looking forward to your help,

Nick

More on how to convert snellen visual acuities to logmar if interested in research article: https://pdfs.semanticscholar.org/9645/6b86671320880d6e5e7e9e1745d250a35224.pdf

Preparation

Data Investigation

Accepted answers

tom_montpool

I think this should help you get to your solution.

Snellen.yxmd

All comments

tom_montpool

I think this should help you get to your solution.

Snellen.yxmd

Faronnj

This was exactly what I was looking for, was having trouble with that initial setup.

Very much appreciate you sending a workspace 🙂

Nick

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703