Maximum Value - Newbie Question

[craigwojo]

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

 

[erik.van.geit]

Craig,

Use "validation"

goto menu data / validation
you'll see what you need

kind regards,
Erik

 

[Zack Barresse]

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.

 

[craigwojo]

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

Thank you,
Craig

 

[Zack Barresse]

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!)

 

[Norie]

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.

 

[erik.van.geit]

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

 

[Zack Barresse]

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.

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

 

[craigwojo]

Thank you all.

Craig

 

[erik.van.geit]

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