• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Tier

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.
 

Attachments

  • RamSample.xlsx
    9 KB · Views: 1
So i think i got it... Ive been trying to break it and all seems well. Hopefully I do not break it after I post this :-D...
 

Attachments

  • RamSample.xlsx
    9.1 KB · Views: 3
Back
Top