Link to home
Create AccountLog 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
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
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