Modify if statement & formula

[abberyfarm]

Hi there,

The code below calculates the efficiency of a gear box based on the values in previous cells and set parameters. It has two conditions at the moment, the first being if rc[-1] is 0 then return 0 and the second is ,if the value calculated is greater than 1 then return 1.

My question is how can I modify it to include if the value calculated is less than 0 then return zero.

Thank you

Range("AC2:AC" & Lastrow3).FormulaR1C1 = "=If(if(rc[-1] = 0, 0, (rc[-2] * rc[-1])/ ((rc[-2] * rc[-1]) + (" & ksl & " * " & Gearratio & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * (rc[-2]*rc[-1])))>1,1,if(rc[-1] = 0, 0, (rc[-2] * rc[-1])/ ((rc[-2] * rc[-1]) + (" & ksl & " * " & Gearratio & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * (rc[-2]*rc[-1]))))"

 

[VoG]

Try

Range("AC2:AC" & Lastrow3).FormulaR1C1 = "=If(if(rc[-1] = 0, 0, max(0,(rc[-2] * rc[-1])/ ((rc[-2] * rc[-1]) + (" & ksl & " * " & Gearratio & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * (rc[-2]*rc[-1])))>1,1,if(rc[-1] = 0, 0, (rc[-2] * rc[-1])/ ((rc[-2] * rc[-1]) + (" & ksl & " * " & Gearratio & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * (rc[-2]*rc[-1])))))"

 

[abberyfarm]

No luck, That is returning an "object defined error"

 

[VoG]

Your original code gives me that error. Does it work for you?

 

[abberyfarm]

It works for me fine?, the two conditions included

I'll copy it again

Range("AC1").Value = "Gearbox efficiency"
    Range("AC2:AC" & Lastrow3).FormulaR1C1 = "=If(if(rc[-1] = 0, 0, (rc[-2] * rc[-1])/ ((rc[-2] * rc[-1]) + (" & ksl & " * " & Gearratio & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * (rc[-2]*rc[-1])))>1,1,if(rc[-1] = 0, 0, (rc[-2] * rc[-1])/ ((rc[-2] * rc[-1]) + (" & ksl & " * " & Gearratio & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * (rc[-2]*rc[-1]))))"

 

[VoG]

Maybe (doesn't error but I have no idea if it gives a correct result)

Range("AC2:AC" & Lastrow3).FormulaR1C1 = "=max(0,If(if(rc[-1] = 0, 0, (rc[-2] * rc[-1])/ ((rc[-2] * rc[-1]) + (" & ksl & " * " & Gearratio & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * (rc[-2]*rc[-1])))>1,1,if(rc[-1] = 0, 0, (rc[-2] * rc[-1])/ ((rc[-2] * rc[-1]) + (" & ksl & " * " & Gearratio & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * (rc[-2]*rc[-1])))))"

 

[abberyfarm]

Works perfect!

Thank you