It never feels good when you type in what you are sure is a perfect formula or function, only to get an error message.
Understanding errors and how to fix them is a big part of keeping your data clean, so it's important to know how to deal with issues as they come up, and more importantly, not to get discouraged.
-Coursera/Google Data Analytics certificate program
Spreadsheet Errors and Fixes.pdf
0.61MB
| Error | Description | Example |
| #DIV/0! | A formula is trying to divide a value in a cell by 0 (or an empty cell with no value) | =B2/B3, when the cell B3 contains the v alue 0 |
| #ERROR! | (Google Sheets only) Something can't be interpreted as it has been input. This is also known as a parsing error. | =COUNT(B1:D1 C1:C10) is invalid because the cell ranges aren't separated by a comma |
| #N/A | A formula can't find the data | The cell being referenced can't be found |
| #NAME? | The name of formula or function used isn't recognized | The name of a function is misspelled |
| #NUM! | The spreadsheet can't perform a formula calculation because a cell has an invalid numeric value | =DATEDIF(A4, B4, "M") is unable to calculate the number of months between two dates because the date in cell A4 falls after the date in cell B4 |
| #REF! | A formula is referencing a cell that isn't valid | A cell used in a formula was in a column that was deleted |
| #VALUE! | A general error indicating a problem with a formula or with referenced cells | There could be problems with spaces or text, or with referenced cells in a formula; you may have additional work to find the source of the problem |
[Basic ways to avoid errors]
- Filter data to make your spreadsheet less complex and busy.
- Use and freeze headers so you know what is in each column, even when scrolling.
- When multiplying numbers, use an asterisk (*) not an X.
- Start every formula and function with an equal sign (=).
- Whenever you use an open parenthesis, make sure there is a closed parenthesis on the other end to match.
- Change the font to something easy to read.
- Set the border colors to white so that you are working in a blank sheet.
- Create a tab with just the raw data, and a separate tab with just the data you need.
[Additional resources]
-Microsoft Formulas and Functions
Formulas and functions - Microsoft Support
Overview of formulas in Excel Define and use names in formulas Create a simple formula Display or hide formulas Move or copy a formula Display or hide zero values Use Excel as your calculator Summing up ways to add and count Excel data Count how often a
support.microsoft.com
-Google Sheets Formula Parse Errors
https://www.benlcollins.com/spreadsheets/formula-parse-error/
Formula Parse Errors In Google Sheets And How To Fix Them
Don't get frustrated. I'll help you understand and fix Google Sheets formula parse errors, including #DIV/0!, #VALUE! #REF! #N/A and more.
www.benlcollins.com
'Data Analytics > Excel & GoogleSheets' 카테고리의 다른 글
| Converting data (0) | 2023.06.11 |
|---|---|
| 새로 알게 된 기능 (0) | 2023.05.28 |
| Keyboard Shortcuts for Functions (0) | 2023.05.25 |
| Google Sheets basics (0) | 2023.05.23 |
| Microsoft Excel basics (0) | 2023.05.23 |
