Lookup - V Lookup

[HELP ME]

i am using excel 2 calculate the amount of bonus that an employee i
entitled to. i do not know what function to use (i have been told 2 us
v lookup but i do not know how 2 go about it).

the exact problem is this:
there is a basic wage and on top of that an employee gets paid a bonus
this bonus is calculated by how many sales r made. the table of th
bonuses r given (below):

Commission on Qtrly Sales
Sales per Qtr Percentage
£200,000 and under 0.50
£300,000 and under 0.75
£400,000 and under 1.00
£500,000 and under 1.25
£600,000 and under 1.50
£700,000 and under 1.75
£800,000 and under 2.00
£900,000 and under 2.25
£1,000,000 and under 2.50

i was goin 2 uase an if statement but there r 2 many conditions.

i would b vvv greatful if someone could tell me how best 2 tackle thi
problem. if u could point me in the direction of a good website tha
would also b much appreciated.
thanks 4 ur help in advance




also i atached the file. the cells in yellow on the payments workshee
is where the formulat should go. it is all self explanotary. if u nee
more info on the prob then mail me ([email protected]) or jus
post.

thanks agai

Attachment filename: help-wages.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=39033

 

[Bob Phillips]

Create a table in A1:B9 (or some equivalent) that looks like

- 0.5
200,001 0.75
300,001 1
400,001 1.25
500,001 1.5
600,001 1.75
700,001 2
800,001 2.25
900,001 2.5


Then to calculate a bonus percentage, you use
=VLOOKUP(sales,A1:B9,2,TRUE)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Create a table in A1:B9 (or some equivalent) that looks like

- 0.5
200,001 0.75
300,001 1
400,001 1.25
500,001 1.5
600,001 1.75
700,001 2
800,001 2.25
900,001 2.5


Then to calculate a bonus percentage, you use
=VLOOKUP(sales,A1:B9,2,TRUE)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[HELP ME]

i tried that and: #NAME? came up

the problem is that the bonus has 2 be calculated seperatly 4 all the 4
quaters. (at least i think it does)

 

[Bob Phillips]

Post me the spreadsheet with the #NAME.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[HELP ME]

here it is :

Attachment filename: help-wages.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=39041

 

[Bob Phillips]

Couple of things

- sales was meant to refer to the sales figure you want to lookup in that
row, not the actual word sales
- uses a table range of $M$6:N$14, to preserve it when you copy down
- change M6 to 0, N6 to .5

Your formula might then look like
=VLOOKUP(G5*4,$M$6:$N$14,2,TRUE)
if you use 4 times the quarterly sales

That should do it

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[HELP ME]

sorry 2 be a pest, but it still does not work
now i get #N/A error

the file is attached

thanks again

Attachment filename: help-wages.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=390456

 

[Trevor Shuttleworth]

Put a zero (0) in cell M6

Regards

Trevor

 

[HELP ME]

thanks alot bob and trevor u lot really really saved me.
it all works like clockwork now
i can get a good nights sleep after all :cool

 

[Bob Phillips]

I thought I already said that <G>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Put a zero (0) in cell M6

Regards

Trevor

 

[Trevor Shuttleworth]

Bob

you did ... but it wasn't in the example that I downloaded. And that was
what I did to fix it.

Merry Christmas

Trevor

I thought I already said that <G>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Trevor,

No I appreciate that, I realise the OP missed it, that was why I added <G>.
The way the OP interpreted my original response made me laugh. You can take
a horse to water ...

Enjoy the rest of your day

Bob