# how i do this in EXCEL

F

#### FROM SA

Is it possible to do the following formal by Microsoft Office Excel 2003:
Products as following
If customer buy 10 parts the cost will be 5\$ by parts
If take any number over 10 parts will be 5\$ for first 10 and more will \$4

Example if take 12 parts will be the price as following:
(10*5)+(2*4)=58
And this products price as following
0 part to 10 the price 5\$
11 to 20 the price 4\$
21 to 30 the price 3\$
More than 30 the price will be 2\$

Example

Customer buys 33 parts so we charge him as following

0 -10 we charge him 10 * 5 \$= 50 \$
11 â€“ 20 10 * 4 \$= 40\$
21 -30 10 * 3\$ = 30\$
31 to 33 3 * 2\$ = 6

So the total charge will be 126

please contact me on email: (e-mail address removed)

Hi,
I assume your list is in column A and cost in column B starting in row 2,
Quantities to be calculated in E2

=+IF(E2<=10,E2*B2,IF(AND(E2>10,E2<=20),((10*B2)+(E2-10)*B3),IF(AND(E2>20,E2<=30),((10*B2)+(10*B3)+(E2-20)*B4),IF(E2>30,(10*B2)+(10*B3)+(10*B4)+(E2-30)*B5))))

Bought quantity being in A1
=MIN(A1,MAX(10,10-A1))*5+MIN(20-10,MAX(0,A1-10))*4+MIN(30-20,MAX(0,A1-20))*3+MAX(0,A1-30)*2

Of course xou can shorten 20-10 to 10, etc. but in this style you see the
boundaries an can change them if it becomes necessary in the future.

Regards,
Stefi

â€žFROM SAâ€ ezt Ã­rta:

Hi,

you can also try this. Assume that your data is setup like this in B26:

Lower limit Upper limit Commission rate
- 50,000 0%
50,001 75,000 10%
75,001 90,000 15%
90,001 1,20,000 20%

In cell F3, use the following formula =D3*(C3-B3)+F2 and copy down till cell
F6.

In cell B9, you have 78,000. Now in cell C9, you can use the following
formula
=VLOOKUP(B9,\$C\$3:\$F\$6,4)+(B9-VLOOKUP(B9,\$C\$3:\$F\$6,1))*VLOOKUP(B9,\$B\$3:\$F\$6,3)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

That's really clever and elegant!
Stefi

â€žPecoflyerâ€ ezt Ã­rta:

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.