Excel Forced Input Multiple of 1000

Another issue being faced in creating user entry in Microsoft Excel. There are companies still using Excel as an application basis for users to enter data, so validation in data exist.

Here is the scenario.

  1. There is a calculator which accepts user input.
  2. The input from the user must be validated and be entered in multiple of 1000.
  3. Program should not allow user to proceed unless input field has been corrected.

SPEED background 1000 calculator excel

This is the screen of the calculator created for this illustrative purpose. As displayed, user can only enter value multiple of 1000, so a value of 5000 is valid.

What happens though, if user tried to enter a value other than multiple of 1000, the screen below should appear.error checking speed 1000 calculator

As mentioned the above calculator was meant for illustration purpose. So how do we proceed from here? I will be using Microsoft Excel 2010 for this tutorial.

excel 2010 data validation

Go to Data and click on the Data Validation menu and the screen below should appear.

data validation excel formula

On the Settings tab, choose Custom and key in the Formula as mentioned above.

=IF(RIGHT($fieldNo,3) <> "000", FALSE, TRUE)

Note: Replace $fieldNo with the column for the validation to be done for.

Voila, it should work, but you have 1 more step to complete which is to prompt the error message.

error alert excel formula

Enter your own Title and Error message. Voila, you have just created a validation.

How is this useful?

Do you know that most companies that dwell into shares will deal in 1000s or 100s, having such validation would ease the process. Of course, another shortcut would be just entering without the zeroes and only display after on the report with the added zeroes.

Leave a Reply