01Total, average, highest and lowest
- =SUM(B2:B30) adds B2 to B30.
- =AVERAGE(B2:B30) gives the average.
- =MAX(B2:B30) and =MIN(B2:B30) give the highest and lowest values.
- =ROUND(AVERAGE(B2:B30), 1) rounds the average to one decimal place.
Tip Select a column of numbers and look at the status bar at the bottom right of Excel. The average, count and sum are shown without writing a formula.
02IF: show a different result depending on a condition
Formula: =IF(condition, value_if_true, value_if_false)
- =IF(B2>=60, "Pass", "Fail")
- Nested: =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", "D")))
- In newer Excel, IFS is easier to read: =IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"D")
03COUNTIF and SUMIF: count or add only what matches
- =COUNTIF(C2:C100, "Satisfied") counts how many people chose "Satisfied".
- =COUNTIF(B2:B30, ">=60") counts how many scored 60 or more.
- =SUMIF(A2:A100, "Meals", B2:B100) adds only the amounts whose category is "Meals".
- For several conditions use COUNTIFS and SUMIFS, for example =SUMIFS(B:B, A:A, "Meals", C:C, "October").
04Common formula errors
- #DIV/0! means a division by zero or by an empty cell. Wrap it: =IFERROR(formula, 0).
- #VALUE! means a cell holds text instead of a number. Check for extra spaces or units.
- The formula gives the same result when dragged down: a $ may have locked the reference.
- Text inside a condition must be wrapped in straight double quotes.
Q&AFAQ
Do function names have to be capitalized?
No. Type them in lower case and Excel converts them.
Are function names different in other languages?
In some languages Excel translates function names, for example SUMME in German. The examples here use the English names.
Rather have it done for you?
Document and Excel help, from about us$9. Report writing, data organizing and basic Excel work: tables, formulas, pivot tables and charts, usually delivered within a day or two.