Subtraction brain block

nobbyknownowt · 11-13-2005, 02:35 PM

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

--
nobbyknownowt

**bill k** · 11-13-2005, 03:04 PM

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** · 11-13-2005, 03:07 PM

=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

Ron Rosenfeld · 11-13-2005, 03:50 PM

On Sun, 13 Nov 2005 13:52:41 +0000, nobbyknownowt
<[email protected]> 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