ExcelGuru516
New Member
I have a spreadsheet I am trying to use and I want it to account for number of units across usage. For every unit, you are allowed to use 9,000 feet. For the 2nd tier you are allowed to use 5,000 feet x the number of units. After that you are allowed to use as many feet as you want but at a higher rate. For the sake of this project I am not concerned with rate.
I created a usage column. Usage can be below or above what is in the tier. But I would like the Tier 1, Tier 2, and Tier 3 in Line 7 to be able to calculate how much usage would land in each tier given the usage in Cell A8. In the case of 48,000, I do not exceed the maximum of 54,000 allowed in Tier 1 calculation in cell C5. So Tier 1 should be 48,000. Now imagine, Total usage was 88000, I would need Tier 1 to show 54,000 because that is the maximum allowed, and then tier 2 to show 30,000 because that is the maximum allowed, and then the remainder fills Tier 3. The problem I have having is the formulas using IF statements are creating negatives for me sometimes when I change the number of units or reduce the usage to certain numbers.
Can someone assist? I am not sure if I am being clear. Thank you.
I created a usage column. Usage can be below or above what is in the tier. But I would like the Tier 1, Tier 2, and Tier 3 in Line 7 to be able to calculate how much usage would land in each tier given the usage in Cell A8. In the case of 48,000, I do not exceed the maximum of 54,000 allowed in Tier 1 calculation in cell C5. So Tier 1 should be 48,000. Now imagine, Total usage was 88000, I would need Tier 1 to show 54,000 because that is the maximum allowed, and then tier 2 to show 30,000 because that is the maximum allowed, and then the remainder fills Tier 3. The problem I have having is the formulas using IF statements are creating negatives for me sometimes when I change the number of units or reduce the usage to certain numbers.
Can someone assist? I am not sure if I am being clear. Thank you.