Link to home
Start Free TrialLog in
Avatar of sandramac
sandramac

asked on

Formula Help

Hello All, I have the formula below, I am trying to figure out a way to code it so if E14=0, then in the formula, make E14=.01 (formula b), if E12=0 then formula C, if both E14 and E12 are  0,  then formula D


((((6.11*10*(7.5*E14/(237.7+E14))/(6.11*10*(7.5*E12/(237.7+E12))))
)))*100

Formula B
((((6.11*10*(7.5*.01/(237.7+.01))/(6.11*10*(7.5*E12/(237.7+E12))))
)))*100

Formula C
((((6.11*10*(7.5*E14/(237.7+E14))/(6.11*10*(7.5*.01/(237.7+.01))))
)))*100

Formula D
((((6.11*10*(7.5*.01/(237.7+.01))/(6.11*10*(7.5*.01/(237.7+.01))))
)))*100
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image
((((6.11*10*(7.5*E14/(237.7+IF(E14=0,0.01,E14)))/(6.11*10*(7.5*E12/(237.7+IF(E12=0,0.01,E12)))))
)))*100
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image
Basically for each one, instead of

E14

You get the value from

IF(E14 = 0, 0.01, E14)

so that when E14 is non-zero, it is still E14, but if it is zero, you get 0.01
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wchh
wchh

= ((((6.11*10*(7.5*IF(E14=0,0.01,E14)/(237.7+IF(E14=0,0.01,E14)))/(6.11*10*(7.5*IF(E12=0,0.01,E12)/(237.7+IF(E12=0,0.01,E12))))))))*100
Avatar of wchh
wchh
Change all E14 to 0.01 for E14 = 0, (this formula contains 2 E14)
Change all E12 to 0.01 for E12 = 0, (this formula contains 2 E12)
Thus, the formula is
= ((((6.11*10*(7.5*IF(E14=0,0.01,E14)/(237.7+IF(E14=0,0.01,E14)))/(6.11*10*(7.5*IF(E12=0,0.01,E12)/(237.7+IF(E12=0,0.01,E12))))))))*100
Avatar of byundt
byundt
Flag of United States of America image
Or even a few characters less, though with logic a trifle harder to see:
=IF(E14=0,1/23771,E14/(237.7+E14))/IF(E12=0,1/23771,E12/(237.7+E12))*100