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.

	A	B	C	D	E
1	<strike></strike>	<strike></strike>Usage	Free	Charge 1	Charge 2
2			1-12	13-25	26+
3	481	29	12	13	4
4	457	7
5	132	13	12	1

<tbody>
</tbody>

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.