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

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


--
bill k


------------------------------------------------------------------------
bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821
View this thread: http://www.excelforum.com/showthread...hreadid=484692

=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


--
bill k


------------------------------------------------------------------------
bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821
View this thread: http://www.excelforum.com/showthread...hreadid=484692

On Sun, 13 Nov 2005 13:52:41 +0000, nobbyknownowt
wrote:


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

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

Thats great thanks guys
cheers
nobby

On Mon, 14 Nov 2005 11:15:50 +0000, nobbyknownowt
wrote:


Thats great thanks guys
cheers
nobby

You're welcome.
--ron