If statement help

[bradyj7]

Hi all,

The code below calculates the efficiency of a gearbox using the formular1c1 function. The efficiency is calculated in the last part. However sometimes the figure is greater than 1, which means the efficiency is greater than 100% which is not possible. So my question is how can I modify it so that, if the last term is > 1 than return 1, else just the turn the value. I know I could do it in the next column but they are used up and will take ages to change the subsequent code.

Thank you

Range("r2:r" & LastRow).FormulaR1C1 = "=if(rc[-1] = 0, 0, (rc[-1] * rc[-2])/ ((rc[-1] * rc[-2]) + (" & ksl & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * rc[-2]*rc[-1]))"

 

[doofusboy]

This should do it:

Range("r2:r" & LastRow).FormulaR1C1 = "=IF(if(rc[-1] = 0, 0, (rc[-1] * rc[-2])/ ((rc[-1] * rc[-2]) + (" & ksl & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * rc[-2]*rc[-1]))>1,1,if(rc[-1] = 0, 0, (rc[-1] * rc[-2])/ ((rc[-1] * rc[-2]) + (" & ksl & " * " & Gearratio & " * " & MaxTorque & " * rc[-1]) + (" & km & " + " & kb & ") * rc[-2]*rc[-1])))"