# Formula Troubles

Copper Contributor

# 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?

3 Replies

# Re: Formula Troubles

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)

# Re: Formula Troubles

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.

# Re: Formula Troubles

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.