Hi,

I would like to know how I can create a formula. What I have is two columns
of data. I would like to have the total of the first column subtracted from
the second column. However when subtracting to start from the top of the
second column until that figure reaches 0 and then carry on with the figure
underneath. ie.

B1 = 5
B2 = 10


so if my first column A is 7 I would like the formula to subtract 7 from B1
until B1 reaches 0 and then the remaining amount to be subtracted from B2. So
this would leave 2 left over and then 10-2 would leave 8. Is there an entry I
can do this?

Many thanks.

You need two formulas, not one.

in C1 put
=if(A1-B10,A1-B1,0)
in C2
=if(A1-B10,B2,B2+(A1-B1))

"Andy" wrote:

Hi,

I would like to know how I can create a formula. What I have is two columns
of data. I would like to have the total of the first column subtracted from
the second column. However when subtracting to start from the top of the
second column until that figure reaches 0 and then carry on with the figure
underneath. ie.

B1 = 5
B2 = 10


so if my first column A is 7 I would like the formula to subtract 7 from B1
until B1 reaches 0 and then the remaining amount to be subtracted from B2. So
this would leave 2 left over and then 10-2 would leave 8. Is there an entry I
can do this?

Many thanks.

Hi,

Thanks for your response. However this is not quite what I need. What I have
is:-
A B C
1 10 10
2 5 5
3 11
4 2
5 ttl 15 13 15

Where 5 is the total, and C is the same amount as A unless subtracted by the
total of B. Therefore after the formula the table will look like:
A B C
1 10 0
2 5 2
3 11
4 2
5 ttl 13 2

I need a formula that will start subtracting B5 from C1 until reaching 0 and
then continue with C2 and so on. Also allowing more data to be entered below
row 4.

Hope this makes sense and you can help.....

"Joel" wrote:

You need two formulas, not one.

in C1 put
=if(A1-B10,A1-B1,0)
in C2
=if(A1-B10,B2,B2+(A1-B1))

"Andy" wrote:

Hi,

I would like to know how I can create a formula. What I have is two columns
of data. I would like to have the total of the first column subtracted from
the second column. However when subtracting to start from the top of the
second column until that figure reaches 0 and then carry on with the figure
underneath. ie.

B1 = 5
B2 = 10


so if my first column A is 7 I would like the formula to subtract 7 from B1
until B1 reaches 0 and then the remaining amount to be subtracted from B2. So
this would leave 2 left over and then 10-2 would leave 8. Is there an entry I
can do this?

Many thanks.

It the same formula just looking at different cells
in C1 put
=if(A1-B30,A1-B3,0)
in C2
=if(A1-B30,A2-B4,A2-B4+(A1-B3))



"Andy" wrote:

Hi,

Thanks for your response. However this is not quite what I need. What I have
is:-
A B C
1 10 10
2 5 5
3 11
4 2
5 ttl 15 13 15

Where 5 is the total, and C is the same amount as A unless subtracted by the
total of B. Therefore after the formula the table will look like:
A B C
1 10 0
2 5 2
3 11
4 2
5 ttl 13 2

I need a formula that will start subtracting B5 from C1 until reaching 0 and
then continue with C2 and so on. Also allowing more data to be entered below
row 4.

Hope this makes sense and you can help.....

"Joel" wrote:

You need two formulas, not one.

in C1 put
=if(A1-B10,A1-B1,0)
in C2
=if(A1-B10,B2,B2+(A1-B1))

"Andy" wrote:

Hi,

I would like to know how I can create a formula. What I have is two columns
of data. I would like to have the total of the first column subtracted from
the second column. However when subtracting to start from the top of the
second column until that figure reaches 0 and then carry on with the figure
underneath. ie.

B1 = 5
B2 = 10


so if my first column A is 7 I would like the formula to subtract 7 from B1
until B1 reaches 0 and then the remaining amount to be subtracted from B2. So
this would leave 2 left over and then 10-2 would leave 8. Is there an entry I
can do this?

Many thanks.

Hi,

Sorry for being persistent. What you have responded with is ok for the small
table in my example. However if you change the numbers it does not work eg.

A B C
1 10 1
2 5 3
3 9
4 2

I would want the C1 to display 0 and C2 to display 2. Also my table would
continue down eg.

A B C
1 10 10
2 5 5
3 3
4 4 4
5 3
6 3
7 6
8 7 7
9 2
TTL 17

So after the formula would look like:-

A B C
1 10 0
2 5 0
3 3
4 4 2
5 3
6 3
7 6
8 7 7
9 2
TTL 17

Is this any clearer??

"Joel" wrote:

It the same formula just looking at different cells
in C1 put
=if(A1-B30,A1-B3,0)
in C2
=if(A1-B30,A2-B4,A2-B4+(A1-B3))



"Andy" wrote:

Hi,

Thanks for your response. However this is not quite what I need. What I have
is:-
A B C
1 10 10
2 5 5
3 11
4 2
5 ttl 15 13 15

Where 5 is the total, and C is the same amount as A unless subtracted by the
total of B. Therefore after the formula the table will look like:
A B C
1 10 0
2 5 2
3 11
4 2
5 ttl 13 2

I need a formula that will start subtracting B5 from C1 until reaching 0 and
then continue with C2 and so on. Also allowing more data to be entered below
row 4.

Hope this makes sense and you can help.....

"Joel" wrote:

You need two formulas, not one.

in C1 put
=if(A1-B10,A1-B1,0)
in C2
=if(A1-B10,B2,B2+(A1-B1))

"Andy" wrote:

Hi,

I would like to know how I can create a formula. What I have is two columns
of data. I would like to have the total of the first column subtracted from
the second column. However when subtracting to start from the top of the
second column until that figure reaches 0 and then carry on with the figure
underneath. ie.

B1 = 5
B2 = 10


so if my first column A is 7 I would like the formula to subtract 7 from B1
until B1 reaches 0 and then the remaining amount to be subtracted from B2. So
this would leave 2 left over and then 10-2 would leave 8. Is there an entry I
can do this?

Many thanks.