Another nested formual help request

[ArcticWolf]

My target is 4.6%. If the value in A1 is over 4.6% then I want it to return
that value less 0.25 in column B, if it's below target I want the value to be
4.6%.

A1 = 5% should return in column B 4.75%
A2 = 3% should return column B 4.6%

Thanks in advance.

AW

 

[Pete_UK]

Try this in B1:

=IF(A1>4.6%,A1-0.25%,4.6%)

Hope this helps.

Pete

 

[joeu2004]

My target is 4.6%.  If the value in A1 is over 4.6%
then I want it to return that value less 0.25 in
column B, if it's below target I want the value to be
4.6%.
A1 = 5% should return in column B 4.75%
A2 = 3% should return column B 4.6%

I suspect you want:

=max(A1-0.25%, 4.6%)

Note that that is not exactly what you stated. That formula ensures
that the result is never less than 4.6%. But you said that you want
A1-0.25% if A1 is over 4.6%. That could result in less than 4.6%, for
example if A1 is 4.65%. (The result is 4.4%.)

If you want exactly what you wrote, with a possible result less than
4.6%, you would write:

=if(A1 > 4.6%, A1-0.25%, 4.6%)

 

[ArcticWolf]

Thanks Pete it works but... my requirements have changed as a result of
your solution and I may not need a nested IF command to achieve this now, but
I'll ask anyway...

If they are above target then reduce by 0.25%
If they are under target then reduce by 0.125%
And if the reduction makes the retuned value less than 0, then return value
zero

A1 = 6% return in column B 5.75%
A2 = 4% return in column B 3.87%
A3 = 0.07% return in column B 0 (zero)

Thanks again,

AW

 

[Pete_UK]

Okay, a further slant on it:

=IF(A1<4.6%,MAX(0,A1-0.125%),IF(A1>4.6%,A1-0.25%,A1))

If A1 is exactly on target there is no change.

Hope this helps.

Pete

 

[ArcticWolf]

Brilliant, works perfick... thanks for your help Pete and for your swift
reply(s).

AW

 

[Pete_UK]

You're welcome, AW - thanks for feeding back.

Pete