excel - Errors when nesting IF() formulas -

i'm putting simple spreadsheet calculates tax owed based on few different bands. have 'invoiced amount' cell takes yearly invoicing , applies tax rate based on following conditions:

if 'invoiced amount' below 10, 600 tax owed = 0

if 'invoiced amount' above 10, 600 less 42, 386 tax owed = ((invoiced amount - tax allowance)/100) * 20

if 'invoiced amount' equal or greater 42, 386 tax owed = ((42, 386 - tax allowance)*20)+((invoicedamount - 42, 386)*40)

i overlooking basic here, sure - tax allowance 10, 600 - on 42, 386 worked out @ 20% tax, , earn above 42, 386 charged @ 40% on top...

the more type out more confused am. anyway, here excel formula:

invoicedtotal = p5  taxallowance (10600) ='uk tax figures'!c3  taxallowanceupperband (42386) ='uk tax figures'!g5  taxallowance upper band - tax allowance (31784) = h5  uppertaxallownace band (42386.01) = ='uk tax figures'!f5   =if ((p5)<’uk tax figures’!c3, 0, if(p5>=’uk tax figures’!c3<'uk tax figures'!g5, ((p5-'uk tax figures'!c3)/100)*20, if(p5>='uk tax figures'!f5, ((h5/100)*20)+((p5-'uk tax figures'!f5)*40)) 

at moment i'm getting crazy unexpected values back, calculation wrong... can't see wood trees @ moment, if has thoughts appreciate it! going little crazy here @ moment!

putting formula online excel formula beautifier notice several problems formula:

=if ( ( p5 ) < ’uk tax figures’!c3 , 0 ,     if(         p5 >= ’uk tax figures’!c3 < uktaxfigures!g5,         ( ( p5 - uktaxfigures!c3 ) / 100 ) * 20,         if(             p5 >= uktaxfigures!f5,             ( ( h5 / 100 ) * 20 ) + ( ( p5 - uktaxfigures!f5 ) * 40 )         ) 

• first of all, lack 2 closing parantheses
• furthermore, have invalid conditional in p5>=’uk tax figures’!c3<'uk tax figures'!g5, need change and(p5>=uktaxfigures!c3;uktaxfigures!c3<uktaxfigures!g5).
• taking @ how reference worksheets above, notice seems have 2 different sheets - 1 spaces between words in sheet names, , 1 without. suspect not case, should remove spaces formula.
• you inconsistent whether or not use apostrophes (’) around sheet-names. trying out formula in cell, excel didn't seem them, should go well.
• there seems return-value missing innermost if-statement if false.

guessing bit @ want formula return, end formula:

=if(p5<uktaxfigures!c3;0;if(and(p5>=uktaxfigures!c3;uktaxfigures!c3<uktaxfigures!g5);((p5-uktaxfigures!c3)/100)*20;if(p5>=uktaxfigures!f5;((h5/100)*20)+((p5-uktaxfigures!f5)*40);0))) 

which looks in beautifier:

=if(     p5 < uktaxfigures!c3;     0;     if(         and(             p5 >= uktaxfigures!c3;             uktaxfigures!c3 < uktaxfigures!g5         );         ( ( p5 - uktaxfigures!c3 ) / 100 ) * 20;         if(             p5 >= uktaxfigures!f5;             ( ( h5 / 100 ) * 20 ) + ( ( p5 - uktaxfigures!f5 ) * 40 );             0         )     ) ) 

is close want?

as final word of advice absolute pain write formula - instead can try build bit bit, storing each part of formula in 1 cell see if each of them works. can cut , paste fit in 1 cell. i.e. if in cell a1 store:

=if(p5>=uktaxfigures!f5;((h5/100)*20)+((p5-uktaxfigures!f5)*40);0) 

then can put

=if(and(p5>=uktaxfigures!c3;uktaxfigures!c3<uktaxfigures!g5);((p5-uktaxfigures!c3)/100)*20;a1) 

in a2 , this

=if(p5<uktaxfigures!c3;0;a2) 

in a3. if necessary can pick formulas further apart make them more readable. in end copy formulas cells, , replace references them.

please note not 100 % got correct, without proper data difficult keep track of parantheses , results. should @ least give starting point.