Excel Vlookup question

[nicostallion]

Hi guys I have un problemo!!

im trying to devise a spreadsheet with vlookup basically to calculate the charges of processing someones payroll for them.

The price will depend on the number of employees the client will have.

These are the price listing rules i have been given.

number....... weekly charge.................. monthly charge
1-5........ .......£10.00 per run.....................£15.00 per run
6-10............. £20.00 per run.....................£30.00 per run
11-50....... ....add £1.50 per person .........add £2.00 per person
51-150...... ....add £1.25 per person .........add £1.75 per person
151-300..... ...add £1.00 per person ........add £1.50 per person
300+ ...........add £0.75 per person ........add £1.25 per person

my knowledge is quite basic so i have teied a few times and have managed to do Vlookup if it was based on the table 1-300 with individ price etc and also using quantity price breaks.

But the way my bosses have asked is a combination of both and im really struggling.

any help and advice on this would be greatly appreciated

many thanks

Nico

 

[Dannyh1]

Hi,

could multiple If statement:

Weekly one =

=IF(A1<=5,10,IF(AND(A1>=6,A1<=10),20,IF(AND(A1>=11,A1<=50),1.5*A1,IF(AND(A1>=51,A1<=150),1.25*A1,IF(AND(A1>=151,A1<=300),1*A1,0.75*A1)))))

(apply same for monthly changing values)

 

[taurean]

Welcome to the board!

Here's one more way (see the adjustment made in column A). The other part of the range is removed i.e. 1-5 --> 1, 6-10 --> 6. This way we can perform Range Lookup. See below:

Excel Workbook
	A	B	C	D	E
1	Number	Weekly Charge	Monthly Charge	Number	20
2	1	10.00 per run	15.00 per run	Weekly Charge	add 1.50 per person
3	6	20.00 per run	30.00 per run	Monthly Charge	add 2.00 per person
4	11	add 1.50 per person	add 2.00 per person
5	51	add 1.25 per person	add 1.75 per person
6	151	add 1.00 per person	add 1.50 per person
7	300	add 0.75 per person	add 1.25 per person
Sheet1

 

[nicostallion]

Hi again i think i wasnt so clear on my first post . the puropse of this is to then type in the nuimber of employees and a price be shown based on those rules above.

i was hoping to make it look like this using this example to show what is calculated


I have 55 employees paid monthly. The cost is £117.50 per run. (£30.00 plus 40 extra at £2.00 each plus 5 extra at £1.50 each).


<TABLE style="BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=293 bgColor=#c7dbd1><TBODY><TR style="HEIGHT: 13pt"><TD class=ee106>Weekly paid employees </TD><TD class=ee109><INPUT style="WIDTH: 100%" id=pA2B class=ee111 tabIndex=1 value=0 name=pA2B> </TD><TD class=ee106></TD></TR><TR style="HEIGHT: 13pt"><TD class=ee106></TD><TD class=ee109></TD><TD class=ee106></TD></TR><TR style="HEIGHT: 13pt"><TD class=ee106>Monthly paid employees </TD><TD class=ee109><INPUT style="WIDTH: 100%" id=pA4B class=ee111 tabIndex=3 value=55 name=pA4B> </TD><TD class=ee106></TD></TR><TR style="HEIGHT: 14pt"><TD class=ee106>Your Payroll quote: </TD><TD class=ee112><INPUT style="BORDER-BOTTOM: #000000 0px solid; BORDER-LEFT: #000000 0px solid; WIDTH: 100%; OVERFLOW: hidden; BORDER-TOP: #000000 0px solid; BORDER-RIGHT: #000000 0px solid" id=pA5B class=ee111 tabIndex=-1 value=£27,12 readOnly name=pA5B> </TD><TD class=ee106>per week</TD></TR></TBODY></TABLE>

 

[francomusso]

Here's a working example based on your pricelist:
http://www.francomusso.com/vlookup-and-if-example

Vlookup with range = TRUE got me as far as finding the correct price bracket for each number of customers.

To allow for the add-on prices, I then had to combine this with an if statement to first consider whether the number was less than 11. Anything less than 11 was just lookep up, whereas anything above 11 was multipled by the relevant 'add-on' price + 20

I thought this apporach would be nice and flexible as you'll see if you change any of the weekly charges, monthly charges or Numbers - everything else will update with no changes tot he formulas required.

I've enabled editing on the speadsheet so you can see / copy the formulas