Usage Tier Formula
I am trying to calculate the usage price if, for example, the usage for the month of June is 4,236,000. How Would I write this formula?
I appreciate your assistance.
This is the tier example I have
Per Unit Cost
Tier1 2,000,000-2,999,999. $0.00734
TIer2 3,000,000- 3,999,999. $0.00667
Tier3 4,000,000-4,999,999. $0.00628
Last edited by Danielle Napoli; 07-28-2022 at 10:23 PM.
@Danielle Napoli welcome to the forum
Please read and refer to this link, how to upload attachments,follow mathod 2
https://www.excelforum.com/the-water...his-forum.html
This seems to be a planning solution (SOLVER FUNCTION)
Per Unit Cost ? Right
Pls try this formula
Formula:
=LOOKUP(B2,$B$5:$B$7,$D$5:$D$7)
Last edited by wk9128; 07-28-2022 at 11:57 PM.
When B2 = 4,236,000 => what is your expected result?
Hi Phuocam,
Please see attached for the example of the expected result.
June Usage 4,236,000
Per unit Cost Range Amount
Tier 1 2,000,000 2,999,999 $0.00734 999,999
Tier 2 3,000,000 3,999,999 $0.00667 999,999
Tier 3 4,000,000 4,999,999 $0.00638 999,999
Tier 1 2,999,999 $22,019.99
Additional 999,999 999,999 $6,669.99
Additional 999,999 236,002 $1,505.69
4,236,000 $30,195.68
With a slight change in your layout:
=SUMPRODUCT((B2>B5:B7)*(B2-B5:B7)*E5:E7)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thank you Glenn,
Much appreciated. I have a spreadsheet of usage and entitlements. I do not have many tired contracts but for the ones I do, I am thinking I will add a tab to add the tiers and then link that total to the front sheet where my columns of formulas are on.
Thank you so much. I saw your post last night for someone's question, but until you provide your own example you do not always fully understand.
Best,
Danielle
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks