Formula Troubles

New Contributor

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

@PandaGMD 

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.

@PandaGMD 

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.

S1726.png

 

S1727.png