Advanced · Work and documents · module 109 of 155
Spreadsheets and formulas with AI
Have formulas suggested and explained, and test them before you rely on the result.
After this lesson
You ask for a formula with an accurate description of your data, test it on a small example and recognise empty cells and range errors.
In view · An improvement loop
Testing a formula before you rely on it
Columns, rows, header row, empty cells and the language setting of your spreadsheet.
Have each part explained, so that you know what the formula calculates.
Recalculate a known result and check the range and empty cells.
Part 1 of 3 · Describe your table
Your preference for less movement is on. Choose each part yourself.
Describe your table accurately
A model does not see your spreadsheet by itself. State which columns there are, which rows the data is in, whether there are header rows or totals in between and which program you use. In Excel with a language setting other than English, functions have different names, such as SOM in Dutch or SOMME in French instead of SUM, and there is often a semicolon between the arguments. Also say what the formula should do with empty cells or text in a column of numbers. Use fictional or anonymised data as an example.
Ask for an explanation and test small
Along with the formula, ask for an explanation of each part, so that you understand what it does. Then test it on a small table whose result you can calculate yourself, for example three rows with 4, 6 and 8. Is the total 18 and the average 6? Then add an empty cell and a cell with text and see what changes. An average that counts an empty cell as zero gives a different result from an average that skips it. Choose consciously what is right for your question.
Watch out for ranges, references and silent errors
Check that the range covers all rows and does not include a header row or total row. When you drag a formula to other cells, references can shift; absolute references with a dollar sign prevent that. A formula without an error message can still calculate incorrectly. So compare one result with a manual calculation and look at striking values. For important reports, have a colleague review the formula and note what it calculates.
New practice example
From explanation to a usable result.
Have AI suggest and explain a formula, and test it on a small example whose outcome you know.
Fictional practice situationFictional table: B2 to B5 contain 10, 20, an empty cell and 30. B6 contains the total 60. A suggested formula for the total is =SUM(B2:B6).
View a possible solution
This is how it can be done
The formula includes the total in B6 and gives 120 instead of 60. The correct range is B2:B5. The average with =AVERAGE(B2:B5) is 20, because the empty cell is skipped: (10 + 20 + 30) / 3.
Why this works
A known outcome makes a range error visible straight away. That way you test the formula before you use it on hundreds of rows.
Make it slightly more difficult
Add a cell with the text “n/a”. What does the formula do with it, and is that what you want for this question?
Extra practice, at your own pace. You do not need to submit anything. This does not count towards an automatic assessment.
Your workplace · learning by doing
Make something. See what changes.
Checking the AI connection… You can already view the written example.
Present your assignment to Ende.
Read the answer, change one thing, and compare.
Previously held
New version
You remain in control
What do you want to improve in your next attempt?
On this device only. AI answers remain only in this page memory. This is self-review, not a grade.
Why this approach works
A known outcome makes a range error visible straight away. That way you test the formula before you use it on hundreds of rows.
A step further
Add a cell with the text “n/a”. What does the formula do with it, and is that what you want for this question?
Try & discover · no obligation
Place source and concept side by side
Adjust the values in the concept. See what difference you would give back to the writer.
Fictional table: B2 to B5 contain 4, 6, an empty cell and 8. Checked by hand, the total is 18 and the average is 6 if you skip the empty cell. Compare with what the suggested formulas produce.
| Part | Source | Concept | Check |
|---|---|---|---|
| Total of B2 to B5 | 18 | 18 | Same |
| Average | 6 | 4,5 | Difference |
| Value of cell B4 | Unknown | 0 | Difference |
Targeted feedback you could provide
- Average: This result counts the empty cell as zero. Decide consciously whether that fits your question. Ask to retain the source value "6", unless a change is confirmed.
- Value of cell B4: This value is not known in the source. Do not fill in the empty field yourself. Ask to leave this as unknown.
You are only changing three fixed fields of a fictitious concept. This is not a full document analysis or a check of a real upload.
This trial works locally with fictitious data and does not count towards your lesson completion. It starts anew with a new lesson or reload.
A prompt to get you started
I work in Excel with an English-language setting. Fictional table: column A contains names, column B the number of participations. Row 1 is a header row, the data is in rows 2 to 40 inclusive and some cells in column B are empty. Give a formula for the average number of participations in which empty cells do not count as zero. Explain each part and give a small test example so that I can check the result myself.
Apply it
An AI suggests a formula that calculates total revenue. The result seems high. The range runs from row 1 to row 50 inclusive; row 50 already contains a total.
Which approach best fits this situation?
- Trust the formula, because no error message appears.
- Adjust the range so that the header row and total row fall outside it, and check the result with a manual calculation.
- Round the result so it stands out less.
With JavaScript, you can check your approach and receive immediate feedback.
Test your insight
A formula for the average gives 4.5 for the values 4, 6, an empty cell and 8. What is probably going on?
- The empty cell is being counted as zero.
- The average is correct, because there are four cells.
- The formula calculates with rounded values.
Extra practice
Create a test sheet with five fictional rows, including an empty cell and a cell with text. Try the suggested formula and check the result once by hand.
=AVERAGE(B2:B40) skips empty cells and text in Excel. Test example: 4, 6, empty and 8 gives (4 + 6 + 8) / 3 = 6. If I counted empty cells as zero, I would get 18 / 4 = 4.5. I also check that B1, the header row, is not in the range.
