Forum Discussion
Formula Troubles
Hey guys, have a quick question about limiting the use of formulas. Right now, I have =ROUNDDOWN(xxxx,-2)-200. xxxx represents a 4-digit numbed entered. I’ve tested it, and that part works fine. (i.e. If 1461 is entered, it yields 1200).
I want to limit the formula such that values less than 1400 do not work, and values greater than or equal to 2301 don’t work. Does anyone know how I could achieve this?
It's not entirely clear to me what you want. Perhaps
=ROUNDDOWN(MAX(MIN(xxxx, 2300), 1400), -2)-200
or
=MAX(MIN(ROUNDDOWN(xxxx, -2)-200, 2300), 1400)
I guess I should be more clear. The user enters the value ‘xxxx’. I want to limit that value such that ‘xxxx’ is greater than or equal to 1400 and less than or equal to 2300.
Thanks. If it's about the result of the formula, use the first one I posted:
=ROUNDDOWN(MAX(MIN(xxxx, 2300), 1400), -2)-200
if you actually want to limit the value that the user can enter in cell xxxx:
- Select that cell (or all cells whose value you want to limit this way).
- On the Data tab of the ribbon, click 'Data Validation'.
- Select 'Whole Number' or 'Decimal' from the Allow drop-down. depending on what you want.
- Enter 1400 in the Minimum box and 2300 in the Maximum box.
- If you wish, activate the Error Alert tab.
- Enter an appropriate message.
- Click OK.
