Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert Visual Acuity Snellen to LogMAR

Faronnj
8 - Asteroid

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 scPreop VA ccBCVA
20/138520/100 
20/270020/3020/30
20/270020/4020/30
2/500 2/500
20/260020/100 
20/250  
20/250  
20/240020/100 
20/265020/100 
20/150020/10020/50
20/105020/4020/40
20/90020/3020/30
20/265020/6020/60
20/210020/6020/60
20/350020/50 
20/380020/60 
10/500 10/400
20/15501/8001/800
20/165020/16020/80
20/25020/125 
20/190020/7020/50
20/180020/6020/50
20/215020/10020/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.

Faronnj_0-1578418742731.png

 

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

 

 

3 REPLIES 3
tom_montpool
12 - Quasar

I think this should help you get to your solution.

Faronnj
8 - Asteroid

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

 

Very much appreciate you sending a workspace 🙂

 

Nick 

Faronnj
8 - Asteroid

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:

LastPreop IOL Ref SphPreop VA sc
Patient 1-9.50 
Patient 2-15.50 
Patient 2-15.50 
Patient 3-20.252/500
Patient 4-15.00 
Patient 5-7.0020/100
Patient 5-5.0020/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" ?

 

Labels