help with if statement

[bradyj7]

Hi there,

The code below calculates the efficiency of a system. Both if statements return 0 if rc[-3] is 0. The first if statement returns 1 if the efficiency is calculated to be greater than 1. The second statement returns all values less than 1.

My question is, how can I modify it so that, if the value returned is less than 0 then return 1?

Range("AJ2:AJ" & Lastrow).FormulaR1C1 = "= If(If(rc[-3] = 0, 0,(rc[-6] * rc[-5]) / ((rc[-6] * rc[-5]) + (" & kesscond & " * rc[-6]^2)))>1,1,If(rc[-3] = 0, 0,(rc[-6] * rc[-5]) / ((rc[-6] * rc[-5]) + (" & kesscond & " * rc[-6]^2))))"

Thank you

 

[sijpie]

How would you do it if it was a normal fomula in Excel? This is no different. You also have an inefficiency in your formula, as you check a second time if rc[-3] = 0, which can not be since it already passed the first time.

Range("AJ2:AJ" & Lastrow).FormulaR1C1 = "= If(If(rc[-3] = 0, 0,If((rc[-6] * rc[-5]) / ((rc[-6] * rc[-5]) + (" & kesscond & " * rc[-6]^2)))>1,1,If((rc[-6] * rc[-5]) / ((rc[-6] * rc[-5]) + (" & kesscond & " * rc[-6]^2)))< 0, 1,(rc[-6] * rc[-5]) / ((rc[-6] * rc[-5]) + (" & kesscond & " * rc[-6]^2))))"

 

[bradyj7]

Hi,

Thanks for the reply. Would you know where the error is with the syntax, I think its a bracket. I can't see it and keeps giving an error.

Thank you

 

[jasonb75]

What values does the varaible "kesscond" return? Can it ever be negative?

 

[bradyj7]

Hi,
The variable 'kesscond' represents losses in the system. Its a decimal number but never less than zero. The code looks correct, can you see the syntax error?

 

[jasonb75]

I didn't notice an error, but this version should work,

Range("AJ2:AJ" & Lastrow).FormulaR1C1 = "=IF(RC[-3]=0,0,IF(MEDIAN(RC[-6]:RC[-5],0),MIN((RC[-6]*RC[-5])/((RC[-6]*RC[-5])+(" & kesscond & "*RC[-6]^2)),1),1))"

 

[bradyj7]

Thank you

 

[jasonb75]

You're welcome, just to confirm that I got the logic right in that version.

If RC[-3] = 0 return 0 regardless of other values.

Otherwise check result of (RC[-6]*RC[-5])/((RC[-6]*RC[-5])+( kesscond *RC[-6]^2))

Result between 0 and 1, return exact result. Result less than 0 or more than 1, return 1.