Practical Excel skills sharing: 3 tips for quickly calculating mathematical expressions

When we need to uniformly convert mathematical expressions in excel into calculable values, what do friends generally do? This problem seems simple. It seems that it can be solved by just adding an equal sign in front of the expression and pressing the Enter key. But what if it is 1000 rows of data? What about 10,000 rows of data? Making manual changes one by one like this is quite tiring! Below I will share with you 3 batch processing methods to solve the problem in minutes, come and take a look!

"I am a civil engineering surveyor. I survey and map various data outside every day. Due to industry requirements, our data must be written in the form of expressions, but operations When the department makes a budget, the expression must be converted into a number before it can be calculated. Do you guys have any good methods? For N thousand lines of data, if you add '=' line by line and press Enter, it will be too tiring."

This is a question that a classmate actually asked in the group. In fact, it is not only the construction industry, many industries, or some units will definitely require employees to display the calculation process in the form of "expressions" , but such data content does bring a lot of trouble to later calculations. Let’s take a look at the source data first:

If you really don’t know any other method, you can do this:

Manual operation line by line, add "=" before the first character of the expression, and then press the "Enter" key to get the calculation result of the expression. However, these dozen or so test data are quite troublesome to operate. If this was done in actual work, some people would probably go crazy.

1. Use the replacement function of EXCEL

The key to using EXCEL to solve problems is to see how well the user understands the EXCEL function. The deeper you understand, the more ideas you will have for solving problems. Let’s just talk about the first method of manually adding “=". If you change the operation method, you can also get the results in batches.

Is there a "sense of acceleration on the back"?

Step 1:Enter the function=" "&B2; in cell E2; (Connect cell B2 with a space to get the data of cell E2)

Step 2: Drop down to fill the E3:E15 cell range, then copy the E2:E15 cell and paste it as a numerical value;

Step 3:Select the cell range E2:E15, press CTRL H to pop up the "Find and Replace" window, enter a space in the "Find content" field, enter an equal sign in the "Replace with" field, and click "Replace All" button to complete the calculation process.

2. Use the macro table function EVALUATE

Step 1: Select cell F3, press the CTRL F3 key combination, and click "New" in the pop-up "Name Manager" window to pop up the "New Name" window. Enter "JS" in the "Name" field, and enter the function "=EVALUATE(B2)" in the "Reference Location" field.

Step 2: Close the name manager, select the F2:F15 cell range, enter "=JS", and press CTRL ENTER to fill it in. Complete calculation.

Note: Because we use the macro table function, it cannot be saved in an ordinary table, so the table needs to be saved in .XLSM format.

3. Lotus Compatibility Settings

Step 1:In the EXCEL options, select the "Advanced" menu, Scroll down to the end, click the "Convert Lotus 1-2-3 formula" option under "Lotus Compatibility Settings", and then click "OK".

Step 2: Select the expression area in the worksheet, and in the "Data" tab, click the "Sort to Columns" function key to complete The process of converting an expression into a numerical value. (Please see the animation for detailed steps)

It should be noted that if we enter an expression in the cell at this time, EXCEL will automatically convert the expression into a number, so if you still need to display the expression, you must re-enter the EXCEL option and change " Uncheck the box in front of "Convert LOTUS 1-2-3".

[Editor’s Note]

That’s all for today on how to convert expressions into numerical values. The content is very simple and the operation is also very simple, but I believe that in In actual work, you can help many students. Do more operations and summarize more, and try to digest every knowledge point you see in the tribe nest into your own knowledge. This should be something you and we all hope for.

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: 3 tips for quickly calculating mathematical expressions. For more information, please follow other related articles on the PHP Chinese website!