OmniDocHow to write Word formulas to calculate average, multiplication and division
Use formula fields to calculate average, product and division in Word tables, explain how to write AVERAGE, PRODUCT and slashes, as well as how to troubleshoot when decimals, percentages and result errors are reported.
Key points of this article
- Use AVERAGE for average, PRODUCT for product, and use slashes for division. All three can be written in the formula field of a Word table.
- Directional words such as LEFT and ABOVE will be counted according to the cell, and addresses such as B2:D2 will have a fixed range. The two can be selected according to whether the table will increase or decrease.
- After inserting the formula field, press Alt+F9 to check the code, and press F9 to update the result. After the data is changed, select all and then update.
- When unit text or spaces are mixed into the cells, the results may be different from expected. Manual calculation can be done directly in one line.
If you are making a grade table, budget table or experimental data table in Word, you will often encounter this situation: to calculate the average score of a row, you need to multiply the unit price by the quantity, or divide one number by another number. If you calculate it by hand and then fill it back in, you will have to start it all over again once the data is changed. Word's table formulas can write the results directly into cells, and then update them after the source data changes.
This article talks about how to write three types of calculations: average, multiplication and division, and explains how to handle decimals, percentages and when the results report errors. After reading, you will be able to write formulas in your own spreadsheets, and you will also know where to look when the results are incorrect.
How to write average, multiplication and division? One sentence conclusion
Write in formula fields: AVERAGE for average, PRODUCT for product, and slash for division. All three can use direction words such as LEFT and ABOVE, or use cell addresses such as B2 and C3 to specify the grids involved in the calculation.
| your situation | Recommended practices | reason |
|---|---|---|
| A row of several subject scores, find the average score | =AVERAGE(LEFT) | The direction word will count the number grid to the left of the current grid. There is no need to rewrite the address when adding a new data column. |
| Find the average score of several people in a list | =AVERAGE(ABOVE) | The continuous number grid above the statistics is suitable for vertical score tables |
| Multiply two fixed grids, such as unit price multiplied by quantity | =B2*C2 | The address is clear, and readers will know which two cells are counted at a glance. |
| Multiply exactly two numbers on the left | =PRODUCT(LEFT) | will multiply all consecutive numbers on the left. When there are more than two numbers on the left, the result will change. |
| Divide one number by another number | =B2/C2 | Word formulas do not have a separate division function, just use slashes |
Before you start: Confirm these points first
- Whether the number is a pure number. If the cell says "85 cents", or contains unit words such as "yuan" or "%", or uses full-width symbols, the formula may not be able to read the value, and the result may be wrong or inconsistent with expectations. You can move the unit text to the header before calculation.
- Whether the address is correct. The letters A, B, and C are used for columns, and the numbers 1, 2, and 3 are used for rows. The header usually occupies the first row. Before writing the address, count it on the table to avoid counting adjacent cells.
- How to deal with empty boxes and text boxes. When spaces or text boxes are included in statistics, the average value may be higher or lower than your hand calculation result. Check a line with a calculator before deciding whether to complete the data or use an explicit address range instead.
- How many decimal places are needed for the result. The number of display digits of the formula result may not meet your requirements. When you need to fix the decimal places, you must add a format switch to the field code. The steps will be described below.
- Whether to update after data changes. The formula result is a field. If the source data changes, it will not be refreshed automatically. It needs to be updated manually, otherwise the old numbers will still be in the table.
Steps to insert average and product formulas into Word tables
- Open the document and place the cursor in the empty cell where you want to display the results.
- Press Ctrl+F9 to insert a pair of field brackets, which will appear as background-colored curly braces. Note that this pair of brackets must be generated using shortcut keys. Curly brackets entered directly from the keyboard will not be recognized as fields.
- Enter the formula between the brackets, such as =AVERAGE(LEFT). If you want to calculate the upper column, write =AVERAGE(ABOVE).
- Press Alt+F9 to switch to displaying field codes, check spelling and brackets for completeness, and press Alt+F9 again to switch back to results view.
- Select the field, press F9 to update, and the calculation result appears in the cell.
- When two decimal places are needed, open the field code, add the format switch at the end of the formula, write =AVERAGE(LEFT) \# "0.00", and then press F9 to update.
- Insert multiplication and division in the same way, such as =B2*C2 or =B2/C2, and the result can be obtained after updating.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Is the result a number? | The view displays as numbers, with no "Error!" Mark | Check if the address points to the text grid, delete the unit character, then press F9 to update |
| Is the statistical scope correct? | Press Alt+F9 to view LEFT, ABOVE, or the address in the code | Change to a clear interval like B2:D2 to avoid crossing into the table header or total rows |
| Whether the empty slots are counted | Use a calculator to manually calculate one row or one column for comparison | Complete the data, or modify the formula to include only the cells that are needed |
| Check if the decimal place is correct | Look at the number of digits in the results | Add a format switch like \# "0.00" in the domain code |
| Have all formulas been updated? | After modifying the source data, observe whether the results change | Select all documents and press Ctrl+A, then press F9 to update all fields |
📊 Core Comparison
| Writing style | Suitable situation | Caution is needed |
|---|---|---|
| LEFT, ABOVE | Row or column data are continuous, and cells may be added later | If there are spaces or text grids in between, the statistics will be broken there |
| Specific addresses, such as B2:D2 | The calculation scope is fixed, such as only counting the first three courses | After adding or deleting columns, you need to manually modify the address |
| Multiply addresses one by one, for example, B2*C2 | Only two numbers need to be multiplied | When there are many grids, the formula becomes longer and is less concise than PRODUCT |
❓ Frequently Asked Questions (FAQ)
1. How do you use the Word formula to divide percentages?
Write the formula as =B2/C2*100, resulting in a number like 25, then manually enter the percent sign after the field. Directly using =B2/C2 gives 0.25, which does not automatically become a percentage.
2. How do you calculate with a Word formula that keeps one decimal place?
Add the format switch \# "0.0" after the formula in the domain code, for example, =AVERAGE(LEFT) \# "0.0", then press F9 to update. If you need two decimal places, just change 0.0 to 0.00.
3. How do you write the multiplication formula for the two left cells in Word Auto?
If there are exactly two number cells on the left side, you can use =PRODUCT(LEFT). To specify clearly, write the specific address, for example =D2*E2, where D and E are the columns with the two left cells. LEFT counts all consecutive number cells on the left; if there are other numbers on the left, the results are multiplied together.
4. The formula results show Error! What should be done?
Most of the time, it is due to a misspelling of address, a reference to a literal grid, or a spelling error in the domain code. Press Alt+F9 to view the code, and after correction, press F9 to update. If errors still occur, check each referenced grid individually to see if there are non-numeric characters.
📝 Summary
Write formulas in Word tables: use AVERAGE for average, PRODUCT for multiplication or direct multiplication, use slashes for division, and add format switches when you need a decimal or percentage for the result. Before starting, confirm the numeric format and statistical scope, then check the line after writing. Mastering this set of writing methods will basically cover common needs for calculating averages, multiplication, and division with Word formulas.
