Split unit usage in tier columns

[Issie_52]

Hi there,

Hoping someone can help me.

I need to split usage of units in different columns (without using VBA):

For example-
Total Usage: 29 (B3)
Column C: The first 1 to 12 unit are free
Column D: Units 13-25 are charge 1
Column E: Units 26+ are charge 2

I want to the split the usage automatically into the different columns with a formula.
Is this possible?

Any help would be appreciated. Please note that I am not allowed to use VBA on the system.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]<strike></strike>
[/TD]
[TD]<strike></strike>Usage
[/TD]
[TD]Free
[/TD]
[TD]Charge 1
[/TD]
[TD]Charge 2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1-12
[/TD]
[TD]13-25
[/TD]
[TD]26+
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]481
[/TD]
[TD]29
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]457
[/TD]
[TD]7
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]132
[/TD]
[TD]13
[/TD]
[TD]12
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you.

 

[Special-K99]

Why is C4 blank?
Shouldnt it be 7 following the same rules as the other two rows?

 

[Rick Rothstein]

Try putting these formulas in the indicated cells and then copy them down to the end of your data...

C3: =MIN(12,B3)

D3: =IF(B3>12,MIN(13,B3-12),"")

E3: =IF(B3>25,B3-25,"")

 

[Issie_52]

Why is C4 blank?
Shouldnt it be 7 following the same rules as the other two rows?

Hi,

They only used 7 units, thus less that the 12 free units.

 

[Issie_52]

Hi Rick

Made a mistake and it didn't work. Sorted it out.

Thank you so much for your quick reply and solution.