# Excel Help

#### Stratos

Associate
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

Associate
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

Soldato
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

Associate
OP
legends! thankyou!