Hello Everyone,
My apologies in advance for the title to this post as it may not be 100% indicative of what I am trying to achieve here. On paper, I know exactly how to make a calculation like this, but I am struggling and possibly overcomplicating how I need to do this.
In short, I am attempting to create a very specific cost calculator for specific teams in my organization. They will only need to input 2 values (a specific "volume type" and a "size input") and the rest should be output for them. I have most of my logic down at this point but was recently told that one of the costs we are calculating is not as simple as what I was thinking.
For context, I originally interpreted it as being if X number was between a specific range (0 to 32000, 32001 to 64000, 64000+), the cost per X would be calculated by a specific number. Thus I would only need to evaluate where that number falls under. However, I was told now that if that value of X was to be something like 36000, I would need to essentially calculate 32000 times one cost and the remaining 4000 by another cost to get that total. See the little cost matrix I put together for reference.
Cost Matrix
0.065 0-32000
0.046 32001-64000
0.032 >64000
I'm not sure how I can easily make a calculation for this. Do I do it based on some type of matrix/table? Any assistance would be appreciated. Sample workbook included for reference. Any guidance would be greatly appreciated as I am 90% sure I am over-thinking this. The 2 inputs I mentioned above are cells D3 and D4. The logic that would need to be changed to accommodate this is in cell D9.
Thank you in advance.
Bookmarks