how i do this in EXCEL
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(E210,E2<=20),((10*B2)+(E 2-10)*B3),IF(AND(E220,E2<=30),((10*B2)+(10*B3)+(E2-20)*B4),IF(E230,(10*B2)+(10*B3)+(10*B4)+(E2-30)*B5)))) "FROM SA" wrote: 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: |
how i do this in EXCEL
FROM SA;254822 Wrote: 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: You can also try =SUMPRODUCT(--(A1{0;10;20;30});A1-{0;10;20;30};{5;-1;-1;-1}) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=71140 |
how i do this in EXCEL
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: 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: |
how i do this in EXCEL
Hi,
you can also try this. Assume that your data is setup like this in B2:D6: 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 "FROM SA" <FROM wrote in message ... 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: |
how i do this in EXCEL
That's really clever and elegant!
Stefi Pecoflyer ezt Γ*rta: FROM SA;254822 Wrote: 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: You can also try =SUMPRODUCT(--(A1{0;10;20;30});A1-{0;10;20;30};{5;-1;-1;-1}) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=71140 |
All times are GMT +1. The time now is 04:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com