Capping a value

[Derrigan]

Hi There,

I'm trying to complete some pension information but can't quite figure out how to cap.

The lower is 6249 and the upper is 50270

I need to be able to return a cap that if a salary (based on a broad increase) achieves or exceeds the upper cap (50270), the calculation is made on the cap, rather than the salary

For example
If the salary is above the cap of £50,270, the calculation will always be 50270 - 6249 / 100 * 4 (or whatever the pension contribution is)
If the salary is below the cap of £50,270, the calculation will be the actual salary - 6249 / 100 *4 (or whatever the pension contribution is)

Thanks, in advance for your assistance!

 

[AlanY]

with salary in A1, try

=(min(A1,50270)-max(A1,6249))/100*4

 

[AlanY]

to cover salary < 6249;

=IF(A1<6249,6249/100*4,(MIN(A1,50270)-6249)/100*4)

 

[Derrigan]

Thank you, so much! Worked perfectly!

 

[AlanY]

Thank you, so much! Worked perfectly!

you're welcome