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!
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.
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.
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.
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.
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.
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.
=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)) .
