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

Try this in B1:

=IF(A14.6%,A1-0.25%,4.6%)

Hope this helps.

Pete

On May 14, 12:42*pm, ArcticWolf
wrote:
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

On May 14, 3:42*am, ArcticWolf
wrote:
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%)

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" wrote:

Try this in B1:

=IF(A14.6%,A1-0.25%,4.6%)

Hope this helps.

Pete

On May 14, 12:42 pm, ArcticWolf
wrote:
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

Okay, a further slant on it:

=IF(A1<4.6%,MAX(0,A1-0.125%),IF(A14.6%,A1-0.25%,A1))

If A1 is exactly on target there is no change.

Hope this helps.

Pete

On May 14, 1:35*pm, ArcticWolf
wrote:
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" wrote:
Try this in B1:

=IF(A14.6%,A1-0.25%,4.6%)

Hope this helps.

Pete

On May 14, 12:42 pm, ArcticWolf
wrote:
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- Hide quoted text -

- Show quoted text -

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

AW

"Pete_UK" wrote:

Okay, a further slant on it:

=IF(A1<4.6%,MAX(0,A1-0.125%),IF(A14.6%,A1-0.25%,A1))

If A1 is exactly on target there is no change.

Hope this helps.

Pete

On May 14, 1:35 pm, ArcticWolf
wrote:
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" wrote:
Try this in B1:

=IF(A14.6%,A1-0.25%,4.6%)

Hope this helps.

Pete

On May 14, 12:42 pm, ArcticWolf
wrote:
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- Hide quoted text -

- Show quoted text -

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

Pete

On May 14, 3:23*pm, ArcticWolf
wrote:
Brilliant, works perfick... thanks for your help Pete and for your swift
reply(s).

AW