[Help] Excel Formula

[A mex eyecan]

I’m putting together a spreadsheet that will calculate tax liability on gross incomes etc.

cell A1 = Total taxable income
cell B2 = Annual Allowance
cell B3 = tax percentage
cell B4 = the missing formula

in short as example I want B4 to calculate the tax payable, but in some cases the taxable income will be lower than annual allowance so not tax payable. Obviously if income is 8k and allowance is 12.5 then the answer should be zero whereas if income is 25k then tax should be £2,500

what should the formula be?

 

[KZNSeagull]

=if(a1<b2,0,(a1-b2)*b3%).

 

[Dick Swiveller]

IF(A1-B2 <0,0,A1*B3)

Something along those lines. Basically, if A1 is less than B2 then make it zero else calculate your tax. Not sure you want A1*B3 but you get the idea

Edit - GAH!

 

[A mex eyecan]

=if(a1<b2,0,(a1-b2)*b3%).

thank you

 

[Driver8]

Is everyone’s income less than £50k? You will need to allow for higher rate tax otherwise.

And National Insurance.

 

[cloud]

Something like =IF(A1 <=A2,0,(A1-A2)*A3)

 

[Stephen Seagull]

Gotta love an IF formula

 

[Fungus]

Okay, since that one's already been solved...

I have two columns - year of birth and year of death. I want to calculate the (approximate) age at death, but only if BOTH the columns have a value, ie. so it doesn't produce an error. Simple if you know how!

TIA!

 

[A mex eyecan]

Is everyone’s income less than £50k? You will need to allow for higher rate tax otherwise.

And National Insurance.

no, all will be either retired, part retired or part time. so all earning less than £750 per month, but will have varying amounts of interest off savings and investments of which some will be taxable. Some will be getting dividends, taxable at 7.5% after the first 2k tax free, some will be taking a draw down from pension pots but are wanting not to draw so much as to trigger much in the way of tax.

 

[clapham_gull]

Gotta love an IF formula

It's immediate IF to be technical.

 

[clapham_gull]

okay, since that one's already been solved...

I have two columns - year of birth and year of death. I want to calculate the (approximate) age at death, but only if both the columns have a value, ie. So it doesn't produce an error. Simple if you know how!

Tia!

=if( and(a1>0,b1>0, b1>a1), b1-a1,"")

Excel will never produce an error since it will treat the blank cell as zero.

So the above checks both numbers are more than zero and checks death (B1) > birth (A1). Unless someone died in the first year of death.... in which case just

=if( and(a1>0,b1>0), b1-a1,"")

 

[spongy]

I opened this thread hoping i could help a little as i used excel at school back in 1995. I have no idea what you're all on about..... Will a hammer help?

 

[SIMMO SAYS]

I hide all my money under the bed

 

[dejavuatbtn]

If (grandmother + balls) = grandfather

 

[Shropshire Seagull]

Try this

 

[Fungus]

=if( and(a1>0,b1>0, b1>a1), b1-a1,"")

Excel will never produce an error since it will treat the blank cell as zero.

So the above checks both numbers are more than zero and checks death (B1) > birth (A1). Unless someone died in the first year of death.... in which case just

=if( and(a1>0,b1>0), b1-a1,"")

Great, thanks. Will try it tomorrow.

 

[oxymoron]

What is the formula to get a year date to change automatically ie 2020 this year 2021 next any help gratefully received

 

[PeterOut]

What is the formula to get a year date to change automatically ie 2020 this year 2021 next any help gratefully received

Use the year(today) function -
https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9

 

[oxymoron]

Thank you Peterout