Excel Formula Question

[Jmcb1b]

Is it possible to take the sum of an equation and put part of it in one
cell and the remainder in another?

example:

sum of cells A1:A4 = Total

cell A5 = (Total from A1:A4) 8 or less (cannot show value higher than
8)

cell A6 = remainder if > 8 if not cell A6 = 0

I just don't know if this can actually happen in Excel or not. Any help
is greatly appreciated.

 

[Andy B]

Hi

Try this in A5

=MIN(SUM(A1:A4),8)

and in A6:
=max(0,SUM(A1:A4)-8)

Andy.

 

[BrianB]

Copy and paste these into the formula bar :=
In Cell A5 : =IF(SUM($A$1:$A$4)<=8,SUM($A$1:$A$4),0)
In Cell A6 : =IF(SUM($A$1:$A$4)>8,SUM($A$1:$A$4),0)