Got something to say or just want fewer pesky ads? Join us... 😊

Register Log in

[Help] Excel Formula



A

A mex eyecan

Well-known member
Nov 3, 2011
3,301
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?
 






Dick Swiveller

Dick Swiveller

Well-known member
Sep 9, 2011
9,154
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!
 




Driver8

Driver8

On the road...
NSC Patron
Jul 31, 2005
15,975
North Wales
Is everyone’s income less than £50k? You will need to allow for higher rate tax otherwise.

And National Insurance.
 






Stephen Seagull

Stephen Seagull

Well-known member
Oct 6, 2015
452
Barcelona
Gotta love an IF formula
 


F

Fungus

Well-known member
NSC Patron
May 21, 2004
7,045
Truro
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

A mex eyecan

Well-known member
Nov 3, 2011
3,301
Driver8 said:
Is everyone’s income less than £50k? You will need to allow for higher rate tax otherwise.

And National Insurance.
Click to expand...

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

clapham_gull

Legacy Fan
Aug 20, 2003
25,296
oxy mcfloppie-tish said:
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!
Click to expand...

=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

spongy

Well-known member
Aug 7, 2011
2,764
Burgess Hill
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?
 










F

Fungus

Well-known member
NSC Patron
May 21, 2004
7,045
Truro
clapham_gull said:
=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,"")
Click to expand...

Great, thanks. Will try it tomorrow.
 


O

oxymoron

Active member
NSC Patron
Feb 25, 2011
179
What is the formula to get a year date to change automatically ie 2020 this year 2021 next any help gratefully received
 






You must log in or register to reply here.

Share this page

Albion and Premier League latest from Sky Sports


Top
Link Here