help with IF statement

[graemespence1]

Hello i'm new to the forum and need help with something.

I'm trying to make a IF statement for a payslip system for a college project the only thing i need help is that i want it so that when the gross pay is above 100,000 i want it so that for every 2 pound that is above the 100,000 limit i want a pound to be removed from the personal allowance limit

text taken from HMRC stite From the 2010-11 tax year the Personal Allowance reduces where the income is above £100, 000 - by £1 for every £2 of income above the £100,000 limit. This reduction applies irrespective of age.

Is there any way to do this as i'm stumped and any help would be gratful and I would include you as a reference.

Thanks and see ya later

[daddylonglegs]

Assuming you have the Personal Allowance in A1 and the Annual Income in B1 this formula in C1 will give you the revised Personal Allowance (Note that it remains the same if B1 is less than £100,000

=IF(B1="","",MAX(0,A1-MAX(0,B1-100000)/2))