Others

Calculate Average from Text Combo Box Values in Access

John WilsonJohn Wilson Nov 3, 2026 869 views

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.

Calculate an Average from Text-Based Combo Box Values in Access
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.
Before you start

Ensure you have a backup of your Microsoft Access database before modifying table relationships or altering the Row Source properties of your combo boxes.

Solution 1Recommended

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.

1
Create a lookup table

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'.

2
Change the combo box Row Source Type

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'.

3
Set the Row Source

In the 'Row Source' property, select the new lookup table you just created.

4
Adjust Column properties

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).

5
Calculate the average

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.

Use a Lookup Table to Bind Numeric Values
Bound Column Property: Ensure the 'Bound Column' property in the Data tab is set to 1. This guarantees that Access stores the hidden numeric 'AnswerNumber' for your calculations.
Free Microsoft Office alternative

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. 1. Download WPS Office: Visit the official WPS Office website and download the free installation package.
  2. 2. Open WPS Spreadsheet: Launch WPS Spreadsheet to handle data entry, tracking, and calculations without the complexity of a relational database.
  3. 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.
Highly compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Easily replicate text-to-number dropdown calculations using WPS Spreadsheet's Data Validation and VLOOKUP functions.Lightweight suite that consumes fewer system resources.Completely free to download and use for everyday office tasks.
microsoft office alternative - wps office

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.