I'm new to the community and was trying to find a solution for a tiered pricing module. I see a few solutions but they tend to be hard-coded with the tiers and rates directly in the formula. I'm curious if it can be dynamic where I can change the rates, or tiers in the cells without having to redo the formula. When I change a number to reference the CELL in some of the examples they no longer work.
It looks like SUMPRODUCT is the right answer but I've seen a couple using MIN and MAX but those seem rather convoluted.
One solution was close but didn't work for quantities over the max number. In other words any number over "26" produced the same result.
I've attached the worksheet with some example results (calculated manually) and was hoping someone would be able to help us out. It seems the formula works based on the difference so I added that column as well.
The Tiers:
First site = $2,500
2 through 5 = 1,000
6 through 10 = 750
11 through 25 = 500
26 + = 250
The reason we want it dynamic is we may want to change the second tier from $1,000 to $1,250 (or 850) just to see how that impacts a deal.
Any help would be appreciated.
Regards,
Mike
Bookmarks