IF Formula

[KMAccountant]

Im doing someones accounts and i need to use the IF FORMULA to work out the values, so between if there wage is between £5000 and £45000 that week (lets say) i want there wages to be charged at 7.5% VAT and it works out the values for me. So when it goes over £45+k i want it to be charged at 20% and so on... Can someone make me formula for it? Thanks

 

[Scott Huish]

You don't need an IF you need a LOOKUP table, like I put in Column J & K in my example. Just put the low end of the range in the left column and the corresponding % in the right column.

=A1*LOOKUP(A1,J:K)

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	45000	9000								Wage	VAT %
2	5001	375.075								0	0
3										5000	0.075
4										45000	0.2
Sheet1

Cell Formulas
Range	Formula
B1	=A1*LOOKUP(A1,J:K)
B2	=A2*LOOKUP(A2,J:K)

 

[KMAccountant]

Thanks!

 

[KMAccountant]

You don't need an IF you need a LOOKUP table, like I put in Column J & K in my example. Just put the low end of the range in the left column and the corresponding % in the right column.

=A1*LOOKUP(A1,J:K)

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
Wage	VAT %

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]45000[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5001[/TD]
[TD="align: right"]375.075[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]0.075[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]45000[/TD]
[TD="align: right"]0.2[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=A1*LOOKUP(A1,J:K)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=A2*LOOKUP(A2,J:K)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!