AVERAGE vs AVERAGEA vs DAVERAGE Function in Excel + Tips (Video in Khmer)

Hello everyone! Today i will show you the difference between AVERAGE, AVERAGEA, and DAVERAGE functions in Microsoft Excel step by step.

What is the Difference?

All 3 formulas help you calculate average value in Excel, but they treat data differently:

• AVERAGE: Calculate average of numbers only. Automatically ignores text cells, words, and logical values (TRUE/FALSE).
• AVERAGEA: Calculate average of ALL values in cell range. Counts text string (like "N/A" or "Absent") as 0, TRUE as 1, and FALSE as 0!
• DAVERAGE: Database formula that calculate average number in a table column matching specific criteria range box.

📚 Related Tutorial: Need average formula with conditions? Check out How to Use DAVERAGE Function in Excel!

🛠️ Formula Syntax

=AVERAGE(number1, [number2], ...)
=AVERAGEA(value1, [value2], ...)
=DAVERAGE(database, field, criteria)

• AVERAGE: Give range of cells containing numbers (example: =AVERAGE(B2:B6)).
• AVERAGEA: Give range of cells containing numbers, text, or logic (example: =AVERAGEA(B2:B6)).
• DAVERAGE: Main database table range, field column name/index, and criteria table range box (example: =DAVERAGE(A1:C6, "Score", E1:F2)).

📋 Sample Data Table (Follow Along)

To test how each formula works, create this sample table in cells A1:B6:

📊 Simple Examples & Comparison

Example 1: Standard AVERAGE(B2:B6)

=AVERAGE(B2:B6)

👉 Calculation: Only numbers 10 and 20 are included. (10 + 20) / 2
👉 Result: 15
(Note: Text "Absent", logical TRUE, and blank cell B6 are completely ignored!)

Example 2: AVERAGEA(B2:B6) (Includes Text & Logical)

=AVERAGEA(B2:B6)

👉 Calculation:

• 10 = 10
• "Absent" = 0 (Text counted as 0)
• TRUE = 1 (TRUE counted as 1)
• 20 = 20
• Total sum = 10 + 0 + 1 + 20 = 31. Total items = 4. 31 / 4
  👉 Result: 7.75
  (Note: Blank cell B6 is ignored, but text and logical cells change the average drastically!)

Example 3: DAVERAGE(A1:B6, "Score", E1:E2) (With Criteria Box)

Criteria box in E1:E2: Student = "Sok"

=DAVERAGE(A1:B6, "Score", E1:E2)

👉 Result: 10 (Calculates average score for Sok matching criteria box).

⚡ Quick Comparison Table

📹 Video Tutorials in Khmer

1. How to Use AVERAGE Function in Excel (Video in Khmer)

2. How to Use AVERAGEA Function in Excel (Video in Khmer)

3. How to Use DAVERAGE Function in Excel (Video in Khmer)

💡 Quick Tips for AVERAGE vs AVERAGEA vs DAVERAGE

1. Use AVERAGE for Most Tasks: For normal math calculations, use AVERAGE so text like "N/A" or "Pending" does not pull down your average to 0.
2. Be Careful with AVERAGEA: AVERAGEA treats all text strings as 0 value. If your student score table has text "Absent", AVERAGEA will count it as 0!
3. Blank Cells Are Always Ignored: All 3 functions skip completely empty blank cells without counting them in the total item count.
4. Use DAVERAGE for Complex Header Filters: When you have big table database and want to average based on criteria box headers, DAVERAGE is super fast.

📚 Related Resources & Tutorials

• How to Use DAVERAGE Function in Excel
• How to Use DSUM Function in Excel
• How to Use DMIN and DMAX Function in Excel
• How to Use AVERAGE Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗