# Maximum Value - Newbie Question

#### craigwojo

##### Board Regular
How do I set the maximun value in a cell. I have a cell that should not exceed 16. How is this done?

Thankyou,
Craig

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Craig,

Use "validation"

you'll see what you need

kind regards,
Erik

Hi Craig,

Select that cell, go to Data --> Validation. Select Custom in the top drop down box and use a formula like this ...

=A1<=16

Assuming that you are in cell A1; change as needed. You can set custom popup messages if you'd like. You can 'mark' it with Conditional Formatting if that would be easier. That would be doing things like changing the font or background color of it when such a condition exists (that you specify), but with no popup.

What about a minimum and a maximum setting for that cell. Can you have to validation settings?

Thank you,
Craig

Yes. Use Whole Number or Decimal. Set your minimum or maximum. If you still wish to use Custom, a formula like this would suffice ...

=(A1>=0)*(A1<=9)

.. where 0 and 9 were your lower/upper boundaries.

(Hiya Erik!)

Craig

Just try Data>Validation... then chose Whole Number from th allow dropdown.

You should now be able to enter min/max values.

If it's not whole numbers you are after you could choose Decimal from the dropdown.

If that doesn't work then perhaps you could explain further what values you want the user to be restricted to in the cell.

Hi, fyrefitr!

Don't you like Decimals or whole numbers from the dropdown?
Or perhaps you've got a good reason to prefer custom formulas?

kind regards,
Erik

My initial habit is to always use Custom. As I should have mentioned that first, it came up in the second post I made. I see my delay got your's and Norie's attention. But yes, they work just as well.

erik.van.geit said:
Hi, fyrefitr!

Don't you like Decimals or whole numbers from the dropdown?
Or perhaps you've got a good reason to prefer custom formulas?

kind regards,
Erik

Thank you all.

Craig

craigwojo,
you're welcome

firefytr,

Yours has an extra advantage !
with two named ranges
minimumcell
maximumcell
you can do this

if there are a lot of cells to validate you could use
if then you need to adjust the validationlimits it's quick done changing the reference-cells
=(A1>=minimumcell)*(A1<=maximumcell)

so, good idea, firefytr!

kind regards,
Erik

Replies
32
Views
540
Replies
7
Views
118
Replies
4
Views
377
Replies
3
Views
200
Replies
2
Views
508

1,202,904
Messages
6,052,464
Members
444,584
Latest member
gsupike

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back