Excel Help

Stratos · 18 Sep 2009 at 14:45

Thought id post in here to get a better response!

The max number i want in a cell is 20, when say 26 is entered in want the 6 to be placed in another cell.

Any help would be great!

CBJ · 18 Sep 2009 at 14:50

Without using a macro, any value entered into a cell is going to overwrite any formula you put there to limit the range. What you need to do is to use a second cell below the one you want the use to fill in to handle the limiting.

There are lots of ways to do it, but a simple one would be to put something like:

=MIN(A1,20)

in the cell you wanted to contain the value limit to 20, and:

=MAX(0,A1-20)

in the cell you wanted the remainder to go into.

Then any calculations you did would go from those cells rather than the one the value was originally typed into.

Edit: Please note that I've not got Excel handy so I've not checked that MIN/MAX are actually the right function names.

Rich_L · 18 Sep 2009 at 14:52

I'd have a column with the numbers, then in two adjacent columns

Column A - Numbers
Column B - =IF(A1>20,20,A1)
Column C - =(A1-B1)

Mmmmmmbodgejob

Stratos · 18 Sep 2009 at 15:13

legends! thankyou!