Excel Help

Associate
Joined
6 Jan 2005
Posts
545
Location
Portsmouth
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

CBJ

Associate
Joined
1 Aug 2007
Posts
344
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.
 
Top Bottom