• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating Price based on incremental staggered quantity

S

suhailsheriff

New Member
Hi, I need a formula that would compute the incremental price based on staggered pricing structure.


For example:

1-100 $5

101-500 $4

501-1000 $3

1001+ $2


Such that if you have say 200 units, the total would be (100*5) + (100*4) = $900

Likewise, if you had 600 units, the total would be (100*5) + (400*4) + (100*3) = $2,400


Please help!


Thanks.
 
Interesting Problem yar,


in the next cell type 100, 400, 500 & 501


Ex 101-500 $4 u want to take only 400 right so type 400


=IF(F2>D2,D2,F2)*E2


EX: C has 1-100, type 100 in D E has $5, in F enter your figure, in G type the above formula

Assuming u have entered u r figure in F1, in F2 F1-G1.


Hope it is clear to u
 
Hi ,


You can have 3 different methods for solving this :


1. Using helper columns


2. Without using helper columns


3. Using a VBA procedure


Which one do you prefer ?


Narayan
 
Hi suhailsheriff,


Welcome to the Chandoo's Forum! Is this list exhaustive or its just a sample data?


Regards,

Faseeh
 
Hi suhailsheriff,


Although I think many people use vlookups way to much for unnecessary things, I believe this is a great problem where vlookup's power is usefull.


Since I am an advocate of using assumption cells, I suggest you make a table with the values of quantities and prices. (I'm sorry, but I'm not sure how to make tabs in the posts to make it more table like)

A1 A2

1 QTY Price

2 1 5

3 101 4

4 501 3

5 1001 2


In the third column add the following formula, strting from the second row: =(A3-A2)*B2+C2

A1 A2 A3

1 QTY Price

2 1 5 0

3 101 4 500

4 501 3 2100

5 1001 2 3600


Then, if cell E1 is where you input you quantity, cell E2 would be: =VLOOKUP(E1,A2:C5,2)*(E1-VLOOKUP(E1,A2:C5,1)+1)+VLOOKUP(E1,A2:C5,3)


I like this formula because it is very dynamic and you can always change the size of the array to accommodate more values.


What do you say?
 
@marninei


Hi!


Give a look at the three green sticky topics at this forums main page, there you will find general guidelines for using it (introducing you to the community, posting netiquette rules, uploading files, ...). As a shortcut, embed within bacticks ` the code you want to preserve in a wysiwyg manner.


About your formula for third column (C) second row (2), which is C2, so you get a circular reference when you type it. Maybe you can fix it? Thanks.


Regards!
 
https://docs.google.com/open?id=0B1_gNYG-sjMoUnc3Nk5qQXhRWGFIRXZ2b0pvUHVUZw


First post on here, got it to work. The formula isn't dynamic right now, in that you cannot change the quantities, but it could be done with a little more time.


Let me know if you have a problem viewing the file.


Cheers
 
Hi, suhailsheriff!


Let's say this is your data starting from A1:

-----

[pre]
Code: 
QTY	950	QTY up to	Cost	Distribution	Cost segment
Total	3550	100		5	100		500
500		4	500		2000
1000		3	350		1050
999999		2	0		0
[/pre]
-----


Input:

B1

C2:D5 (cost structure)


Formulas:

B2: =SUM(F:F)

column E (E2:E5): =IF(B$1-SUM(E$1:E1)>C2,C2,B$1-SUM(E$1:E1))

column F (F2:F5): =D2*E2


Just define properly columns C:D for your cost structure.


Here's the link to the file, if needed: http://dl.dropbox.com/u/60558749/Calculating%20Price%20based%20on%20incremental%20staggered%20quantity%20%28for%20suhailsheriff%20at%20chandoo.org%29.xlsx


Regards!
 
@SirJB7,


Hope you dont take me wrong.


The initial requirement states, if qnty is greater than 100, and upto 500, then 100 will be at 5 and the remaining 400 will be at 4. But your post shows you have considered 500 in the distribution instead of 400.


Although I have not checked the calculation also has same issue. Per his requirements for Qnty 950, the price shall be 3450.


All,

I thought of using IF condidtions to solve this like this:


Assuming in Col A I have QTY up to and Col B I have Cost and qnty ordered (input) in cell C2, then use formula in Cell D2 as follows:


QTY up<tab> Cost<tab> Qnty Ordered <tab> Calc Price

100<tab> 5<tab> 950<tab> 3450'(result of formula)

500<tab> 4

1000<tab> 3

999999<tab> 2


Paste the below formula in D2 and drag down as required.


=IF(C2<=$A$2, C2*$B$2, IF(C2<=$A$3, $A$2*$B$2+(C2-$A$2)*$B$3, IF(C2<=$A$4, $A$2*$B$2+($A$3-$A$2)*$B$3+(C2-$A$3)*$B$4, ($A$2*$B$2+($A$3-$A$2)*$B$3+($A$4-$A$3)*$B$4+(C2-$A$4)*$B$5 ) )))


Regards,

Prasad DN
 
@prasaddn

Hi!


I'd never take you wrong, and even less if I made a mistake; I'd rather thank you for rectifying it so as the original poster don't get confused and so I can realize if I didn't post what I tried to mean.


Now, let's go to the point:

- as you stated I understood that from 1-100 the cost was 5, from the 101 up to the 500 it was 4, and so on

- in the uploaded example I wrote a qty of 950 (B1) and that was distributed in E2:E5 in 100/500/350/0 which sums 950... which is wrong!!!


So here's the update:

Change formula in E2 from

=IF(B$1-SUM(E$1:E1)>C2,C2,B$1-SUM(E$1:E1))

to

=IF(B$1-SUM(E$1:E1)>C2,IF(ISNUMBER(C1),C2-C1,C2),B$1-SUMA(E$1:E1))

and copy down thru E5.


You'll get a distribution of 100/400/450/0 which sums 950... which it now is right thanks to you, Prasaddn!


All other formulas in column F and in B2 work properly.


Again, thanks for you correction.


Regards!


PS: a humble comment about IF's solution, if the differential price levels grow it'll be harder to maintain the nested IFs structure.
 
@suhailsheriff


Hi!


I apologize for the mistake and for the inconveniences it might have caused you.

Please update formulas in E column as indicated in previous post.

Sorry for the error.

Regards!


PS: just updated the uploaded file, download it again and replace previous one for getting the corrected version
 
thank you my friend SriJB7,


I agree with limitation of IF formulas.


Regards,

Prasad DN

PS: But, where is suhailsheriff?? hope he checks these posts. :)
 
Hi All,

[pre]
Code: 
Threshold	Price	Price Difference
0	            5	      5
100	            4	     -1
500	            3        -1
1000	            2	     -1

=SUMPRODUCT((B1>A6:A9)*(B1-A6:A9)*(C6:C9))
[/pre]
Where B1 Contains the volume of units, A6:A9 is the threshold range and C6:C9 is the price difference range.
 
Greetings all,
I am trying to solve an identical task.
@Kyle - tried to copy the data and formula suggested above. Unable to copy the frmula, #62 is highlighted as if it is an error. What does #62 stand for.
 
Hello All
here is my task I am dealing with.

Package RangeRate
1 to 207
21 to 509
51 to 7511
>75
13

for package count of 80 I should get the result (20*7+30*9+25*11+5*13) = 140+270+275+65. I am rtying to use sunproduct. Greatly appreciate any advise.

Regards
Adi
 
You must log in or register to reply here.
Back
Top