Excel formula question

[Richard]

I’m befuddled...

I’m trying to make a formula in Excel (learned on Lotus) and since they seem to do it differently, I’ve become confused.

In LOTUS, what I think my formula would be is

“IF((J4-I3)/I3)<=0,((J4-I3)/I3),IF((J4-I3)/I3)>0#and#((J4-I3)/I3)<=.0325,((J4-I3)/I3),If((J4-I3)/I3)>.0325,.0325”

What I’m trying to do is find a rate of return. If the rate of return is less than zero, enter the negative. If it’s UP to and including 3.25%, then use the real number, if it’s greater than 3.25%, then use 3.25% as a cap.

Anyone care to push (or kick) me in right direction in Excel?

(gentle kicks preferred) /forums/images/graemlins/tongue.gif

 

[Richard]

Finallllllllllllllly figured out

disregard request & thanks for reading

Brain been asleep for last 24 hours on this.

/forums/images/graemlins/tongue.gif

=+IF(J9/I8<0,(J9/I8),IF(J9/I8<=0.0325,(J9/I8),0.0325))

is my formula.

A bit different in mechanics (calculating gain/loss percentage) than above, but what I noticed after I posted it, was I already had the column showing net change..blah blah blah.

Seems to work thusfar, but I DO wish I could fine tune the <=.0325 AND >0, but oh well.

/forums/images/graemlins/cool.gif

 

[Gatorboy]

Why don't you just use

IF( J9/I8 <= 0.0325, (J9/I8), 0.0325)

The <0 check is not necessary.

 

[Richard]

</font><font color="blue" class="small">( Why don't you just use )</font>


I'm laughing outloud at how simple/clear that is (now).

Thanks, I'll go change & simplify it.

Actually, it DID cross my mind (though barely) if I was making it more complicated.