Help needed in transposing a number with specific rules
having trouble with foruma for last colum ( Axis) the maximum value of the Axis can only be 180. If 100 degrees is added then the vaule will be 80.
I have steps 1 and 2 which are simple adding forumla but I cant work out the formula for step 3 as shown below
Transpose -3.00 +2.00 x 30
| Sphere | Cylinder | Axis |
| -3.00 | +2.00 | 30 |
Step 1 Add the sphere and cylinder powers to determine the new sphere power
(-3.00) + (+2.00) = -1.00
Step 2 Change the sign of the cylinder -2.00
Step 3 Change the axis by 90 degrees. (if the axis is 90 or less than 90, add 90 degrees to the axis, if the axis is greater than 90 deduct 90 from the axis) 120
The transposed prescription is: -1.00 -2.00 x 120
| Sphere | Cylinder | Axis |
| -1.00 | -2.00 | 120 |
Any help how I can use a formula for step 3 or a macro function to switch back and fourth between the transposed prescription . Thanks you
Solution
First select the three cells with the values and run this macro:
Sub transpose()
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3],RC[-2])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=-RC[-3]"
If ActiveCell.Offset(0, -2).Value > 90 Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Offset(0, -3).Value-90
Else: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Offset(0, -3)+90
End If
End Sub
It will create the three new values just on the following collumns, try it and tell me want do you think ;)