Excel: Use the actual error function - so it'll work
The actual error function in Excel is especially helpful for very long tables. This practical tip shows you how to use the actual error function in the spreadsheet correctly.
Use the actual error function in Excel
There are many ways that errors can occur in a formula. So that you can still calculate undisturbed, simply use the actual error function. Below we explain this using a very simple example. We keep a small logbook to keep track of our reimbursement of travel expenses:
- In our example, we have entered the date in column B, the days of the week in column C and the kilometers traveled in column D or the reason why we did not drive. In column E we see the respective refund amount for the day.
- On days when you have not driven for any reason, you will see the error message "#Value" in column E. That would not be a big problem at first. However, we would later like to use the values in column E to calculate the total amount for the individual weeks, the month or the entire year. That would be a problem: Excel cannot use strings in an equation.
- Now the actual error function is used: With its help, we replace the error message with a zero. We use the following formula in the formula bar: = If (actual error (D4 * 0.65); 0; (D4 * 0.65)).
- We use this to tell Excel: if an error occurs in the formula, output the value 0 instead of an error message, otherwise calculate the amount. In our case, the travel costs to be received.
- If you then want Excel to calculate the respective totals, you can do so without any problems since the strings have been replaced by a 0.
- Of course, you can also use the actual error function to create more complex formulas or, for example, troubleshooting.
If you are not yet familiar with arithmetic operations in spreadsheets, the article "Arithmetic and formulas with Excel - how it works" is a good help.