Calculate Average from Text Combo Box Values in Access
Question details
The user needs a method to calculate an average score from multiple combo boxes that currently display and store descriptive text ratings instead of numeric values.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating a database form or report where users select descriptive ratings (e.g., '1 - Poor', '5 - Excellent') from combo boxes, and the system must calculate an average score based on those selections.
- Observed behavior
- The combo boxes are currently using a Value List that stores text strings, making it impossible to perform mathematical operations like calculating an average directly from the selected values.
Ensure you have a backup of your Microsoft Access database before modifying table relationships or altering the Row Source properties of your combo boxes.
Use a Lookup Table to Bind Numeric Values
Create a lookup table to store numeric values while displaying descriptive text, allowing for mathematical calculations like averages.
By binding an integer to the combo box instead of a text string, Microsoft Access can perform mathematical functions in the background while still presenting user-friendly text options on the frontend.
Create a new table in Access. Add an integer field named 'AnswerNumber' (this will be the bound value) and a short text field named 'AnswerText' (this will be the displayed value). Populate the table with your rating scale, such as 1 and '1 - Unacceptable'.
Open your form in Design View. Select the combo box, open the Property Sheet, and change the 'Row Source Type' from 'Value List' to 'Table/Query'.
In the 'Row Source' property, select the new lookup table you just created.
In the Format tab of the Property Sheet, set the 'Column Count' to 2. Then, set the 'Column Widths' to '0;1'. This hides the first column (the numeric value) and displays the second column (the descriptive text).
Because the combo box now stores the 'AnswerNumber' as its bound value, you can calculate the average in your query or form by adding the values of the six combo boxes and dividing by six: ([Combo1] + [Combo2] + ... + [Combo6]) / 6.

Try WPS Office for Your Data Management Needs
While Microsoft Access handles complex relational databases, many data tracking and calculation tasks—like rating averages from dropdown lists—can be managed effortlessly in a spreadsheet. WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installation package.
- 2. Open WPS Spreadsheet: Launch WPS Spreadsheet to handle data entry, tracking, and calculations without the complexity of a relational database.
- 3. Use Data Validation and VLOOKUP: Set up drop-down lists using Data Validation, and use the VLOOKUP function to silently convert text selections into numeric values for seamless averaging.

Frequently Asked Questions
Why can't I calculate an average directly from a Value List combo box?
If your Value List only contains text strings (e.g., '1 - Poor', '2 - Fair'), Access treats the selected data as text. Mathematical functions like sum or average require numeric data types to process calculations.
What does setting Column Widths to 0;1 do in an Access combo box?
Setting the first column width to 0 inches hides that specific column (typically the bound numeric ID), while the second column width of 1 inch remains visible. This trick allows users to select descriptive text while the database securely stores the underlying number.
Can I use an IIf statement or Switch function instead of creating a lookup table?
Yes, you can use expressions in a query to convert text values to numbers (e.g., Switch([Field]='1 - Poor', 1, [Field]='2 - Fair', 2)). However, creating a lookup table is widely considered a best practice because it is easier to maintain, scale, and reuse across multiple combo boxes.




