Subtraction brain block

N

nobbyknownowt

Hello all Heeeellllpppp!
Cannot get my head around this and now I'm just getting frustrated.
Hope this is correct bit of the forum!

I have a spreadsheet that calculates the index moment of a bar. Along
the tube there are fluid containers which drain in a specific order.
I need excel to calculate the remaining fluid in the containers once a
certain amount has been removed so I can then calculate the moment
before and after.
Its the subtraction bit I cannot get my head around.

example

container 1 starts with 500
container 2 starts with 0
container 3 starts with 8000
container 4 starts with 1000

If i drain 5500 I am left with

container 1 0
container 2 0
container 3 3000
container 4 1000

How can i set it up so that excel does not return native numbers and
uses the remainder from calculation 1 to start calculation 2

The answer is most likely straight forward I just cannot see it!!
cheers
Nobby
 
B

bill k

this worked on your example

=IF((SUM($A$1:A4)-$B$1)>A4,A4,IF(SUM($A$1:A4)-$B$1<0,0,SUM($A$1:A4)-$B$1))

where B1 is the cell with 5500.

enter the formula in c1 and extend down
 
B

bill k

=IF((SUM($A$1:A4)-$B$1)>A4,A4,IF(SUM($A$1:A4)-$B$1<0,0,SUM($A$1:A4)-$B$1))

oops

enter in C4 and extend up to C1
 
R

Ron Rosenfeld

Hello all Heeeellllpppp!
Cannot get my head around this and now I'm just getting frustrated.
Hope this is correct bit of the forum!

I have a spreadsheet that calculates the index moment of a bar. Along
the tube there are fluid containers which drain in a specific order.
I need excel to calculate the remaining fluid in the containers once a
certain amount has been removed so I can then calculate the moment
before and after.
Its the subtraction bit I cannot get my head around.

example

container 1 starts with 500
container 2 starts with 0
container 3 starts with 8000
container 4 starts with 1000

If i drain 5500 I am left with

container 1 0
container 2 0
container 3 3000
container 4 1000

How can i set it up so that excel does not return native numbers and
uses the remainder from calculation 1 to start calculation 2

The answer is most likely straight forward I just cannot see it!!
cheers
Nobby
Click to expand...

I set up a little table in A1:E4

Container1 Container2 Container3 Container4
Initial 500 0 8000 1000
Drained
Balance

Put the Amount to Drain (AmtToDrain) in some cell and either Name it or use
the cell reference in the formula below.

Enter the following formulas:

B3: =MIN(B2,AmtToDrain)
C3: =MIN(C2,AmtToDrain-SUM($B$3:B3))

Select just C3 and copy/drag across to E3.

B4: =B2-B3

Select B4 and copy/drag across to E4.

-------------------------------

The "Drained" row is how much was drained out of each container.

The "Balance" row is the amount remaining in each container after draining.


--ron
 
Top