Tiered pricing/discount help

[sbsyncro]

I've spent about 4 hours now combing various resources to try to figure this out and nothing I've tried has worked yet (or the explanations have been so esoteric that I simply can't grasp them).

My problem is this:

I have a table with 12 columns, one for each month. Each month will have a quantity value. Based on the following tiered pricing table, I need to calculate the total (qty * price for each tier) for each month.

Pricing tiers:
2015-04-07_12-50-48.jpeg

The key here is that I want to be able to show the calculated total for each tier (for each month). Here's an example of what I'm trying to fill out (I manually calculated the first column just for an example):

2015-04-07_12-55-54.jpeg

I started down a nested IF route, but that became unwieldy very quickly. Then I tinkered with VLOOKUP and ran into a dead end. I've been trying to get my head around SUMPRODUCT, but I can't seem to get anything to work properly.

Any advice would be GREATLY appreciated. I would assume that this is SO COMMON yet, I cannot find good examples. And yes, I have read through http://mcgimpsey.com/excel/variablerate.html and it just didn't help me. I'm a visual guy and need an example that is specific to my need to be able to figure it out, unfortunately.

Thanks!

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mikeTRON]

Can you post a sample workbook that you screenshot?

Also include an example that is hand typed if necessary to determine what it is you are trying to do.
I think we can help, but I cant determine your bucketing really...

[mikeTRON]

I took a stab at it, although your bucketing is still unclear (10k-100k is the first bucket, but what about things UNDER 10k?!).

Anyway, attached is fairly straightforward example. Also worth noting, I used CUMULATIVE tiered pricing, as you will see. So the 1M bucket can only have 500k in it, as the other 500k is in the buckets above.... I was not sure if that was what you wanted but it made the most sense to me.

Tiered Pricing.xlsx

Orange cells are input cells.
Yellow cells are notes.
Light Grey cells are just used for sanity check.
Dark Grey are check cells, to validate everything is being accounted for.

[sbsyncro]

Example.xlsx

here's a mock-up of what I'm trying to accomplish

[mikeTRON]

Attachment 387838

here's a mock-up of what I'm trying to accomplish

Did you take a look at the example I provided for you?

[sbsyncro]

Yes thanks.

I'm trying to weed through it right now. It accomplishes what I'm trying to do, but I'm picking through and fixing each of the row/column references to get it to work in my spreadsheet.

[shg]

Can you explain your example results?

[sbsyncro]

Can you explain your example results?

Argh. The numbers changed when I pasted it to a new spreadsheet for some reason. The total number was originally 390,000. But using $400,000 as in the example, it SHOULD have values of $9,000 and $21,000 in the following rows of the first column.

With a base number of 400,000 records, I apply a rate of $0.00 to the first 10,000 records. Then for records 10,001 to 100,000 a rate of $0.10 is applied (90,000 x $0.10=$9,000), and the remaining 300,000 records are at a rate of $0.07 or $21,000 for a grand total of $30,000

Sorry for the confusion.

[sbsyncro]

I took a stab at it, although your bucketing is still unclear (10k-100k is the first bucket, but what about things UNDER 10k?!).

Anyway, attached is fairly straightforward example. Also worth noting, I used CUMULATIVE tiered pricing, as you will see. So the 1M bucket can only have 500k in it, as the other 500k is in the buckets above.... I was not sure if that was what you wanted but it made the most sense to me.

Attachment 387837

Orange cells are input cells.
Yellow cells are notes.
Light Grey cells are just used for sanity check.
Dark Grey are check cells, to validate everything is being accounted for.

Your assumptions were all correct. Thank you!

I still find myself wondering if there is a more elegant way to do this using either VLOOKUP or SUMPRODUCT (or a combination of the two). But for now, I think I have something that works, thanks to your help. A thousand thanks!

-Brent

[mikeTRON]

Your assumptions were all correct. Thank you!

I still find myself wondering if there is a more elegant way to do this using either VLOOKUP or SUMPRODUCT (or a combination of the two). But for now, I think I have something that works, thanks to your help. A thousand thanks!

-Brent

Haha, yeah your "example" was garbage and misleading so I just tried it in the manner that I have dealt with this in the past (with discounted pricing based on quantity).
Yeah I kept brainstorming for a simple solution but I figured an if statement was simple enough haha. You CAN use a clever array formula to calculate the total Sales dollars, but I prefer to calculate the quantity per bucket first, then simply apply the price to that as it is much easier to audit.

If it is solved, can you mark the thread solved?

Glad I could help!

[sbsyncro]

How do I mark it as solved?

[Pepe Le Mokko]

See the FAQ