Formula help

[Matt_Chelmsford]

I need help with a Formula.

*

In column A1 to A5 I have 5 categories. A B C D E

*

In column B1 to B5 I have the number of units to be charged.
*


The problem I have that the first three units will be free (in the order of A to E)

*

Therefore if in B1 there are 2 units for A and in B2 there are 2 units for B then I want to show in

C1 0 and C2 1.

*

I hope this makes sense

*

Matthew

 

[Andrew Poulsom]

Insert a row above your data (and enter some headings if you want). In C2 enter:

=MAX(0,SUM(B$2:B2)-3)-SUM(C$1:C1)

and copy down.

 

[Yahya]

may be this will help

Excel Workbook
	A	B	C
1	A	2	0
2	B	2	1
3	C	2	3
4	D	2	5
5	E	2	7
Sheet2

Excel 2007

Cell Formulas
Range	Formula
C1	=IF(B1<3,0)
C2	=IF(SUM($B$1:B2)>3,SUM($B$1:B2)-3,0)
C3	=IF(SUM($B$1:B3)>3,SUM($B$1:B3)-3,0)
C4	=IF(SUM($B$1:B4)>3,SUM($B$1:B4)-3,0)
C5	=IF(SUM($B$1:B5)>3,SUM($B$1:B5)-3,0)