Need help with formular for a split in values

Originally Posted by SJC2006

A B
1 113
2 0-60 60
3 60+ 53

Cell B2 =SUM(B1-B3)*(B1>=60)
Cell B3 =SUM(B1>=60)*(B1-60)

What I need it to do is seperate the figure in B1 Between B2 & B3.
If B1 is greater than 60 then 60 goes into Cell B2 and the remainder goes into Cell B3 (which at the moment it does).

My problem is, when the figure in cell B1 is 60 or less, (say 59 then that figure needs to show in Cell B2 and B3 will show as 0. Their is no allowances for a negative figure.

Any advice would be great at the moment I can get it to do either but not both options.

Hi,

Not quite sure on your choice of formula.

To split B1 over B2 - B3 then

in B2

=if(B1>=60,60,B1)

and in B3

=If(B1>=60,B1-60,"")
or
=If(B1>=60,B1-60,0)
or
=If(B1<60,"",B1-60)

as required.

hth
---

Originally Posted by SJC2006

Second option in B2 worked great,
Been a long time since I used Excel (forgot more than I remember)

Kindest regards

SJC

Hi,

Good to see that it worked for you, and thanks for the response.

---