Multiple IF Formula to Calculate Value or End in Zero

[ScottTemple]

Hello,

I have the following formula in cell Q2:

=IF(ISBLANK(F2),"",IF(ISBLANK(P2),"",IF(ISBLANK(K2),"",(((F2*4)/3.14)/(P2*(K2^2)))/12)))

What I would like to add, is that if cell Z2 equals "RE-SPOOL" the formula returns a zero value, otherwise, return the value listed as part of the original formula.

 

[Coyotex3]

Maybe adding an additional if statement at the beginning of your existing formula(Untested)

=IF(Z2="RE-SPOOL",0,IF(ISBLANK(F2),"",IF(ISBLANK(P2),"",IF(ISBLANK(K2),"",(((F2*4)/3.14)/(P2*(K2^2)))/12))))

 

[ScottTemple]

Thanks Coyotex3, however, I'm hoping you can assist me with a follow up change to another formula that is impacted by this one.

In cell S2 I need to change the formula from =IF(Q2="","",IF(I2="","",(Q2/I2))) to something along the lines of =IF(Q2="","",IF(I2="","",(Q2/((F2*4)/3.14)/(P2*(K2^2))/12))). Where Q2 needs to be divided by the result of ((F2*4)/3.14)/(P2*(K2^2))/12

 

[Coyotex3]

Hi Scott, this is a different question. I can try to assist but are you able to share a sample of your data with made up data along with the desired result?

 

[ScottTemple]

Of course. The formula you provided changes column Q to equal zero if 'repolish' is entered into column Z.

Column S (standard run time) is calculated based on Q (footage) divided by I (line speed) - =IF(Q2="","",IF(I2="","",(Q2/I2)))

Column T (variance) calculates the difference between R (actual run time) minus S (standard run time). However, when Q shows 0, the value of T is not correct as we still need to calculate the different between the actual and standard run times (R-S). To get around this, I want to create a formula in S, which is normally =IF(Q2="","",IF(I2="","",(Q2/I2))), and changes the I2 to ((F2*4)/3.14)/(P2*(K2^2))/12 which is the calculation for Q. This will still give me a 0 value in Q, if Z equals respool, but will still calculate for S and provide the correct value for T.

 

[Coyotex3]

I'm somewhat following however, in this instance though, since Q2 is 0 based on "RE-SPOOL" being on cell Z2. Dividing Q2(0) by any number will give you a zero in column S. Isn't that so?

In the example that you shared, what should be displayed(result) in S2 and S3?