Pension Formula - Please help...

[Legacy 102501]

Hey there

I have never posted before, but always read the forum and the help is amazing, but i'm i have one question myself, which i can't find the answer to.

I need to work out a formula that provides the amount of pension costs we have to pay. ie.

If salary is between 0 and £19,500 the rate is 16.7%
If salary is between £19,501 and £40,500 the rate is 18.8%
If salary is between £40,501 and £69,000 the rate is 21.8%
If salary is between £69,001 and above the rate is 24.3%

I can't for the life of me work out a formula that will look at the salary field and calculate the above rates and output the annual amount.

Has anyone go any ideas?

thanks very much in advance

Mike

 

[VoG]

Hi and welcome to MrExcel.

Try

=LOOKUP(A1,{0,19501,40501,69001},{0.167,0.188,0.218,0.243})

 

[MrKowz]

Give this a shot.

The formula I used in B2 is:
=VLOOKUP(B1,A4:B7,2)

Book2
	A	B	C	D
1	Salary:	$15,000
2	Rate:	16.7%
3
4	$0	16.7%
5	$19,501	18.8%
6	$40,501	21.8%
7	$61,001	24.3%
Sheet1

 

[Oaktree]

Welcome to the board.

So we're clear, if a salary is £20,000 should the calculation return £20,000 * 18.8% = £3,760? Or £19,500 * 16.7% + £500 * 18.8% = £3,350.5?

 

[Legacy 102501]

Wow, thank you so much. it has worked.

i can believe how easy it was! my brain was almost fried today.

thanks again