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
1	45000	9000								Wage	VAT %
2	5001	375.075								0	0
3										5000	0.075
4										45000	0.2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =A1*LOOKUP(A1,J:K) B2 =A2*LOOKUP(A2,J:K) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks!