I want to include a formula in a spreadsheet which works out how much personal allowance is available if income in cell a1 is between £100,000 and £116,210.
I tried =if(100000<a1<116210,((a1-100000)/2),0)
It does not reject the formula, but it does not work. Something is wrong with the first bit but I don't know how to include two conditions into the formula.
Any bright sparks out there that can set me straight?
Related resources
Replies (6)
Please login or register to join the discussion.
If
=8105-IF(A1>116210,8105,IF(A1<116210,0,INT((A1-100000)/2)))
or better still (I think)
=8105-MAX(0,MIN(8105,INT((A1-100000)/2)))
Cell A1 contains the adjusted net income.
Cell A2 contains the basic personal allowance (£8,105 for 2012/13)
The following formula will give the personal allowance available:
=A2-IF(A1>100000,(MIN(A1,100000+(2*A2))-100000)/2,0)
=IF(AND(A1>10000),(A1<116210)
=IF(AND(A1>10000),(A1<116210))*(A1-100000)/2
Does that work?
or even MIN & MAX
as above & here to get the allowance reducer:
=MAX(0,MIN(8105,INT((A1-100000)/2)))
Which, to explain:
MIN(8105,INT((A1-100000)/2)) will provide the lesser of 8105 or the Integer result of half income less £100k.
If income is less than £100k the answer will be negative - (90k - 100k)/2 = -5kif income is greater than £100k the answer will be positive - (110k - 100k)/2 = 5k; butif income is greater than £100k + 2 x personal allowance the result will be restricted to 8105
MAX(0,MIN(.... evaluates the greater of 0 and the result of the MIN() formula, so:
if income is less than 100k then the result of MIN() is negative (1 above) & MAX will return 0if income is greater than £100k the result of MIN() is positive and the reducer will be either 2 or 3 above