I'll try my best to explain this with my limited know-how.....
In the past, we had a fixed tiered/bracketed pricing structure, such as what follows:
1-4 pcs. $23/ea,
5-9 pcs $7.50/ea,
10-49 pcs $3.44/ea,
50-249 pcs $2.68/ea
250+ pcs $2.14/ea
All units were charged at the same rate, depending upon the order qty. If a customer ordered 49 pcs., all pieces were priced at $3.44, 50 pcs thru 249 were charged at $2.68/ea, etc. Although pretty simple, this pricing method is not very reflective of actual production costs. The savings a customer can realize by ordering just one more pc to get into to the next lowest tier is usually way out of proportion with any savings in production costs.
Recently, I found an example of an array formula that allows for "progressive pricing", whereby units are not all lumped into one tier for pricing.
=SUM(IF(IF(B$5>=F6:F10,F6:F10-F5:F9,$B$5-F5:F9+1)>0,IF(B$5>=F6:F10,F6:F10-F5:F9,$B$5-F5:F9+1),0)*G5:G9)
This type of pricing is more in line with actual costs per unit as well as giving customers a much needed break in that first (1- 4) tier of the old system. Tentatively, the new pricing structure may look something like this:
1 $23.00/ea
2 $15.00
10 $5.00
50 $1.70
250 ???
From 1 thru 249, I'm very pleased with this method, but when we get into quantities of 250 pcs or more, things get out of whack, and the old fixed tier system is better for pricing those larger qty orders. I don't know how to tell the formula that it should switch gears when it gets to 250+, nor is that addressed in any of the sources I've found. I have the 99999999 in the last cell of the qty column, with a 0 or empty value in its price column/cell, but that doesn't seem to do what I'm wanting to do, and I'm at a complete loss. ANy input will be very much appreciated.
I might as well take a stab at this while I'm here.....IF I can make this work like I'm hoping, the last hurdle will be incorporating it into our bookkeeping program, Quick Books Pro. I've got a sinking feeling that maybe can't be done, but hope springs eternal.....even when your springs are a bit rusty. ;-) If anyone has knowledge or suggestions along those lines, I again will be much appreciative for the help....or guidance as to where I might find it. (Nothing on their website or in program documentation....that I've been able to find anyway.)
I hope I've explained this well enough, and I thank you for your time.
In the past, we had a fixed tiered/bracketed pricing structure, such as what follows:
1-4 pcs. $23/ea,
5-9 pcs $7.50/ea,
10-49 pcs $3.44/ea,
50-249 pcs $2.68/ea
250+ pcs $2.14/ea
All units were charged at the same rate, depending upon the order qty. If a customer ordered 49 pcs., all pieces were priced at $3.44, 50 pcs thru 249 were charged at $2.68/ea, etc. Although pretty simple, this pricing method is not very reflective of actual production costs. The savings a customer can realize by ordering just one more pc to get into to the next lowest tier is usually way out of proportion with any savings in production costs.
Recently, I found an example of an array formula that allows for "progressive pricing", whereby units are not all lumped into one tier for pricing.
=SUM(IF(IF(B$5>=F6:F10,F6:F10-F5:F9,$B$5-F5:F9+1)>0,IF(B$5>=F6:F10,F6:F10-F5:F9,$B$5-F5:F9+1),0)*G5:G9)
This type of pricing is more in line with actual costs per unit as well as giving customers a much needed break in that first (1- 4) tier of the old system. Tentatively, the new pricing structure may look something like this:
1 $23.00/ea
2 $15.00
10 $5.00
50 $1.70
250 ???
From 1 thru 249, I'm very pleased with this method, but when we get into quantities of 250 pcs or more, things get out of whack, and the old fixed tier system is better for pricing those larger qty orders. I don't know how to tell the formula that it should switch gears when it gets to 250+, nor is that addressed in any of the sources I've found. I have the 99999999 in the last cell of the qty column, with a 0 or empty value in its price column/cell, but that doesn't seem to do what I'm wanting to do, and I'm at a complete loss. ANy input will be very much appreciated.
I might as well take a stab at this while I'm here.....IF I can make this work like I'm hoping, the last hurdle will be incorporating it into our bookkeeping program, Quick Books Pro. I've got a sinking feeling that maybe can't be done, but hope springs eternal.....even when your springs are a bit rusty. ;-) If anyone has knowledge or suggestions along those lines, I again will be much appreciative for the help....or guidance as to where I might find it. (Nothing on their website or in program documentation....that I've been able to find anyway.)
I hope I've explained this well enough, and I thank you for your time.