IN EXCEL, I WANT TO USE THE FORMULA TO CALCULATE AND VERIFY THE VALIDITY, HOW DO I DO IT?

I have a suggestion that is.

1. "Use Conditional Formatting".

For example, select "Cell Value" in the box of Conditional Formatting Conditional Formatting Condition 1(1), select "Less than" in the second box in the horizontal direction, enter "80 A2" in the third box, go down, click "Format", and set a validity display format, such as selecting the color "Red" in the font OK, and then "Add" Condition 2(2), set the cell value to be greater than "120 A2, and also set the format to be displayed in red" OK. That is, it is done. If A1 is out of range, the font will turn red.

2. Use the validity, select the "a1" data validity setting custom formula, enter "<80%*a2or>120%*a2", tick the "error warning" style "warning" style "warning" title in the error card, enter "error", and the error message "out of range" OK, complete.

Instead of conditional formatting, set the condition of A1 to be between the ranges, and the cell is represented in red. That's also obvious.

You can also set the data validity to achieve this effect.

For A1, it is a certain change range between A2, and a dialog box will automatically pop up if it exceeds this range.

Very simple.

Try it with the "New Rule" setting in the "Conditional Formatting" subdirectory.

The best way to do this is to use the header footer, copy the header you want to display to the top of the header.

When using excel to work**, it is often necessary to enter a large amount of data, and sometimes the data required to be entered cannot be repeated, such as ID card number, admission ticket number, license plate number, etc., once the input is wrong, it is quite troublesome to find. In order to avoid input errors, we can use custom formulas and validity data check linkage methods, so that Excel can automatically check the duplicate data throughout the process. That is to say, every time a new ID number is entered, the system will check with all the ID numbers that have been entered one by one, and correct the errors in time.

We use Excel 2016 as an example to illustrate that it can also be implemented in other versions of Office.

Correct raw data entry.

Let's take the example of entering the ID number in **. Since entering the 18-digit ID number directly into the system will display it in scientific notation, which is not in line with our habits, we should first select all the ID card columns, right-click and select the set cell, and set it to text format.

Construct verification conditions with formulas.

Select all the cells in the worksheet that you need to set for duplicate checks, and then click the "Data" function menu, and select the "Data Validation" option in the "Data Tools" functional area, and click the first option "Data Validation" in the submenu....”。

Next, select the "Settings" tab in the "Data Validation" dialog box that pops up, and select the "Customize" option in the "Allow" drop-down list box (and select the "Ignore Nulls" option); Enter countif($1$65535,a1)=1 in the text box below "Formula".

The $1 $65535 here represents a duplicate check for the entire worksheet range, which solves the problem of an unknown number of ID numbers, no matter how many personal ID numbers you enter. Of course, if you only need to check a specific range of cells, you can readjust it according to the actual situation, but you must use absolute references.

Set up personalized error alerts.

Switch to the "Error Warning" tab, where the program has automatically checked the checkbox of "Error warning is displayed when entering invalid data", then set the "Style" to the "Stop" option, and then enter the relevant prompt content (such as "ID card duplicate") and the specific prompt message explanation (such as "Please check whether the current ID number is entered incorrectly, or the previous ID number information is entered incorrectly as the current number") , and then click the OK button.

If you encounter a number entered repeatedly in the process, Excel will prompt you. For the input and automatic detection of other types of data, you can follow the above process to flexibly change the type, judgment range, and prompt information content of the cell.