In this lesson
Build a reference sheet that explains each function’s inputs, result and version requirements.
Example
Zero, blank and Pending are different inputs
Type Amount in A1, enter 100 in A2, 0 in A3 and 200 in A4, leave A5 blank and enter Pending in A6. You will compare function results for that range. The date example uses April 6–10, 2026 with April 8 as a practice holiday; the repayment example uses principal 1200 over 12 periods at zero interest.
How it works
COUNT, COUNTA and AVERAGE include different inputs. Zero is a number, a blank has no entry and Pending is text. Decide which records belong in your calculation.
Check the function’s version information before adopting it. Try a normal input and an exception case, especially when the recipient uses an older Excel release.
Steps
Enter the Amount sample. In separate cells, calculate =SUM(A2:A6), =COUNT(A2:A6), =COUNTA(A2:A6) and =AVERAGE(A2:A6). Compare which entries each function includes.
Try =MIN(A2:A6), =MAX(A2:A6) and =COUNTIF(A2:A6,">0"). Check minimum 0, maximum 200 and positive count 2. The zero is a real amount in this sample.
Enter LT01-Laptop in C2. Use =LEFT(C2,4) for LT01 and =MID(C2,6,LEN(C2)-5) for Laptop. These positions fit this sample format; inspect other source formats before reusing them.
Enter =DATE(2026,4,6) in E2, =DATE(2026,4,10) in E3 and =DATE(2026,4,8) in E4. Compare =NETWORKDAYS(E2,E3) with =NETWORKDAYS(E2,E3,E4) to see the holiday exclusion.
Enter =PMT(0,12,1200) in G2. The result -100 represents an outgoing payment. If you later use a nonzero rate, match its period to the payment periods.
Before using a new lookup or array function, read its official version support and test a missing-result case. Keep INDEX/MATCH or ordinary filtering for Excel 2016/2019 recipients who lack XLOOKUP.
Make a card with Function, Question, Sample inputs, Expected result, Version and Edge case. Add one working example from each function family you tried.
Try it yourself
- Replace Pending with numeric 50 on a copy and predict SUM, COUNT, COUNTA, AVERAGE, MIN and MAX before recalculating. Explain which results change and which remain the same.
- Move the invented holiday outside the Monday-to-Friday date interval and predict the workday count. Keep the original card so the two exception cases can be compared.
Show expected result
- Originally SUM is 300, COUNT 3, COUNTA 4 and AVERAGE 100; MIN is 0, MAX 200 and the positive-value count is 2.
- Replacing Pending with 50 gives SUM 350, COUNT 4, COUNTA 4 and AVERAGE 87.5, while MIN stays 0 and MAX stays 200.
- The date interval contains five weekdays, or four when April 8 is excluded. The zero-interest payment is -100 per period; the negative sign represents an outflow.
If something goes wrong
- AVERAGE is lower because it includes zero.
- Check what zero means in the source. It is a real observation here and belongs in the numeric average; a missing-data code would need a different rule.
- A payment or date result looks reasonable but does not fit the question.
- Check rate and period units, the cash-flow sign, DATE arguments and holiday cells. Correct the inputs before changing their display format.
Quick check
Try these questions after the exercise. Check the result in your app before marking the lesson complete.
Choose an answer for all 3 questions to check your understanding.
Optional: try it with Copilot
If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.
Help me make reference cards for these samples. Explain zero versus blank, the April 8 holiday and the negative PMT result, and list older-version alternatives where needed.
More from Microsoft
These Microsoft guides cover the tools used in this lesson.
Loading your progress…