# Quick Excel question: Don't want to see invalid calculations
**URL:** https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819
**Category:** Factual Questions
**Created:** [October 9, 2013, 10:01pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819 "2013-10-09T22:01:35Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [October 9, 2013, 10:01pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/1 "2013-10-09T22:01:35Z")
I have a spreadsheet that looks like this:
```auto
Date Odo Miles Gallons Ind MPG Calc MP Difference Avg. MPG Avg. Error
10/09/13 305.0 7.300 44.2 41.78 5.79% 42.30 3.10%
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
```
At least I hope it does. No telling what it will look like on someone else’s computer.
I’d like to drag the formulas in the last four columns down the page so that the numbers automatically appear when I enter the data. But I don’t want to see any error message for records where there is no data.
How do I hide the error messages until there’s data for the formulas?
EDIT: :smack: I thought I was in GQ!
.
---
### Author:  [@Jonathan\_Chance](https://boards.straightdope.com/u/Jonathan_Chance)
#### Post date: [October 9, 2013, 10:08pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/2 "2013-10-09T22:08:35Z")
Let me just move that for you.
---
### Author:  [@GIGObuster](https://boards.straightdope.com/u/GIGObuster)
#### Post date: [October 9, 2013, 10:13pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/3 "2013-10-09T22:13:22Z")
Ah, good thing we moved, I thought it was going to be a philosophical debate on how there is no such thing as nothing. 🙂
There are several solutions, one common one one is to add a logical conditional like “If” to your formula:
If the formula that produces the error is =A1/A2, use =IF(A2=0,“”,A1/A2) to return an empty string, or =IF(A2=0,0,A1/A2) to return 0.
More explanations and examples with other ways to do that over here:
> **[Excel 'Divide by Zero' Errors Driving You Nuts? Try These Simple Fixes](https://www.timeatlas.com/excel-divide-by-0-error/)**
>
> See how to remove #DIV/0! in Excel and replace it with 0's or blanks. Includes formula examples and screen snaps.
---
### Author:  [@Caldazar](https://boards.straightdope.com/u/Caldazar)
#### Post date: [October 9, 2013, 10:17pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/4 "2013-10-09T22:17:03Z")
=IF(ISERR(b1/c1),"",b1/c1)
---
### Author:  [@tomndebb](https://boards.straightdope.com/u/tomndebb)
#### Post date: [October 9, 2013, 10:18pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/5 "2013-10-09T22:18:17Z")
> [@Johnny\_L.A](#):
>
> How do I hide the error messages until there’s data for the formulas?
I don’t know that it is the best solution, but an easy solution is to place the equation inside an IF statement where “IF Gallons \<\> 0” is TRUE leads to the equation while the FALSE is replaced by a zero or a blank.
---
### Author:  [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)
#### Post date: [October 9, 2013, 10:18pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/6 "2013-10-09T22:18:59Z")
Concur with **Caldazar** on the formula. You can also replace the “” with an error message of your choosing, like “Nope.”
---
### Author:  [@andrewm](https://boards.straightdope.com/u/andrewm)
#### Post date: [October 9, 2013, 10:43pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/7 "2013-10-09T22:43:31Z")
I have no idea what the heuristic is that triggers it, but sometimes when Excel notices that you’re repeatedly entering new tabular data in the first few columns of a row and have expressions adjacent, it will automatically copy the expressions down to the new row for you.
---
### Author:  [@barnhardt999](https://boards.straightdope.com/u/barnhardt999)
#### Post date: [October 10, 2013, 1:11am UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/8 "2013-10-10T01:11:20Z")
=iferror(normal formula here, value if error)
Just use “” or 0 in the value if error spot.
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [October 10, 2013, 1:43am UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/9 "2013-10-10T01:43:35Z")
> [@andrewm](#):
>
> I have no idea what the heuristic is that triggers it, but sometimes when Excel notices that you’re repeatedly entering new tabular data in the first few columns of a row and have expressions adjacent, it will automatically copy the expressions down to the new row for you.
That’s how it used to work. The previous spreadsheet did it for a while, then it stopped doing it. I don’t know why. The new spreadsheet iwas copied from the old one, and all but the first few records were deleted and replaced.
I’ll try the IF statement when I log onto my office computer tomorrow.
Thanks.
---
### Author:  [@j666](https://boards.straightdope.com/u/j666)
#### Post date: [October 10, 2013, 2:33am UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/10 "2013-10-10T02:33:25Z")
> [@KneadToKnow](#):
>
> Concur with **Caldazar** on the formula. You can also replace the “” with an error message of your choosing, like “Nope.”
I strongly recommend using text instead of the blank “”, as “” is not distinguished from an empty cell.
Also, review the results from ISERR() and ISERROR().
---
### Author:  [@mbetter](https://boards.straightdope.com/u/mbetter)
#### Post date: [October 10, 2013, 3:03am UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/11 "2013-10-10T03:03:54Z")
> [@barnhardt999](#):
>
> =iferror(normal formula here, value if error)
>
> Just use “” or 0 in the value if error spot.
This is the preferred way to do it in Excel 2007 and above. The other suggestions are what you would do in Excel 2003.
---
### Author:  [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)
#### Post date: [October 10, 2013, 3:38am UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/12 "2013-10-10T03:38:24Z")
IFERROR is the neatest method IMHO but be mindful the function is a 2007 release.
For users with earlier XLS versions you’ll need the IF(ISERROR/ISERR formats.
Also endorse **J666** comments.
For workbooks I need to show in presentations I use IFERROR(A1/A2,“hide”) and then apply conditional formatting to set the background and text to be the same (usually pale) colour in the cells with “hide” as the result.
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [October 10, 2013, 3:38pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/13 "2013-10-10T15:38:35Z")
OK…
A = Date
B = Odometer miles
C = Gallons of fuel
D = Indicated MPG
E = Calculated MPG
F = Difference between indicated and calculated MPG (%)
G = Average calculated MPG
H = Average error between indicated and calculated MPG
**E (calculated MPG): =IF(C8=0,"",B8/C8)**
This worked. Cell is blank; and when values are entered, a value is displayed.
**F (difference): =IF(E8=0,"",(D8/E8)-1)**
This one didn’t work. #VALUE! is returned.
**G (average mpg): =AVERAGE(E$2:E8)**
This was not changed. It works, based on the change to the formula in E.
**F (average error): =IF(F8=0,"",AVERAGE(F$2:F8))**
This one didn’t work. #VALUE! is returned.
---
### Author:  [@Skammer](https://boards.straightdope.com/u/Skammer)
#### Post date: [October 10, 2013, 6:05pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/14 "2013-10-10T18:05:58Z")
> [@Johnny\_L.A](#):
>
> **E (calculated MPG): =IF(C8=0,“”,B8/C8)**
> This worked. Cell is blank; and when values are entered, a value is displayed.
>
> **F (difference): =IF(E8=0,“”,(D8/E8)-1)**
> This one didn’t work. #VALUE! is returned.
If E8 is blank, then D8/E8 will give you the #value error. I would make this formula =iferror(D8/E8-1,“”) if you are using Excel 2007 or higher.
> [@](#):
>
> **G (average mpg): =AVERAGE(E$2:E8)**
> This was not changed. It works, based on the change to the formula in E.
>
> **F (average error): =IF(F8=0,“”,AVERAGE(F$2:F8))**
> This one didn’t work. #VALUE! is returned.
If F8 is an error or empty (“”) you will get an error with this formula as well. You could use =iferror(average(F$2:F$8,“”) instead.
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [October 10, 2013, 6:17pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/15 "2013-10-10T18:17:01Z")
> [@Skammer](#):
>
> You could use =iferror(average(F$2:F$8,“”)) instead.
‘You’ve entered too few arguments for this function.’
[ul][li]=IFerror(E8=0,“”,(D8/E8)-1)[/li]‘You’ve entered too many arguments for this function.’
[li]=IFerror((D8/E8)-1)[/li]‘You’ve entered too few arguments for this function.’[/ul]
---
### Author:  [@mbetter](https://boards.straightdope.com/u/mbetter)
#### Post date: [October 10, 2013, 6:25pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/16 "2013-10-10T18:25:29Z")
> [@Johnny\_L.A](#):
>
> ‘You’ve entered too few arguments for this function.’
>
> [ul][li]=IFerror(E8=0,“”,(D8/E8)-1)[/li]‘You’ve entered too many arguments for this function.’
>
> [li]=IFerror((D8/E8)-1)[/li]‘You’ve entered too few arguments for this function.’[/ul]
You want exactly two arguments: the expression you want to evaluate and the value that should be returned if the first expression happens to be an error.
```auto
=IFERROR((D8/E8)-1,"")
```
---
### Author:  [@Skammer](https://boards.straightdope.com/u/Skammer)
#### Post date: [October 10, 2013, 6:54pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/17 "2013-10-10T18:54:43Z")
Oops, right, sorry. In that second formula I gave you I messed up the parentheses.
=iferror(average(F2:F8),"")
=iferror(D8/E8-1,"")
should work for the other cell. You have two arguments: the value (D8/E8-1) and the output if there is an error ("").
Incidentally, you can use parents in the expression (D8/E8)-1 but they are not necessary. Division is done before subtraction so D8/E8-1 gives the same result and you have fewer parentheses to keep track of.
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [October 10, 2013, 6:55pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/18 "2013-10-10T18:55:43Z")
> [@mbetter](#):
>
> ```auto
>
> =IFERROR((D8/E8)-1,"")
>
> ```
=IFERROR((D8/E8)-1,“”) worked, or seems to. I say ‘seems to’ because I tried to modify two more cells.
=IF(D8=0,“”,AVERAGE(E$2:F8)) for average MPG. The previous values are 42.09, 43.56, 42.31, 42.58, 42.41, and 42.30. When I put the formula in, the answer is 21.13 – which is obviously incorrect. (When there is no value in D8, the cell is blank.)
For average %error, I tried =IF(D8=0,“”,AVERAGE(F$2:F8)). Pulling out my calculator and adding up the values in F (difference), including one dummy value, and dividing by 7, the value displayed is the value I calculated. When D is empty, nothing is displayed in the cell.
So I’m almost there. Can you help me out with the code for average MPG? I’m trying =IF(D8=0,“”,AVERAGE(E$2:F8)) .
---
### Author:  [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)
#### Post date: [October 10, 2013, 6:55pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/19 "2013-10-10T18:55:50Z")
I didn’t realize they’d added the IFERROR function. That’s a good improvement.
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [October 10, 2013, 6:57pm UTC](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819/20 "2013-10-10T18:57:22Z")
> [@Skammer](#):
>
> Oops, right, sorry. In that second formula I gave you I messed up the parentheses.
> =iferror(average(F2:F8),“”)
>
> Incidentally, you can use parents in the expression (D8/E8)-1 but they are not necessary. Division is done before subtraction so D8/E8-1 gives the same result and you have fewer parentheses to keep track of.
I caught the missing parenthesis. I like to use parentheses even when not required, for clarity. 😉
[Next page](https://boards.straightdope.com/t/quick-excel-question-dont-want-to-see-invalid-calculations/670819.md?page=2)