MS Excel assistance, plz

[Feral_Collie]

Good Morning, SCOA.

I am making a fuel log using MS Excel to record my mileage, but have ran into a snags. My setup has odometer readings from start to finish, distance covered, gallons filled at the pump, and mileage.

My snags are that my programming for the mileage column has my first entry, then multiple #DIV/0! as there is no fuel entry. Any way to clean that up?

Also, I want to make a cumulative mileage counter, which will make an average of the mileage column while ignoring the zero entries. I thought I had it, but I ended up getting a cumulative MPG of 1.7 for entries 1-25.

Any MS Office savvy people out there that can fix my hack job?

[doenut]

Originally Posted by Feral_Collie

Good Morning, SCOA.

I am making a fuel log using MS Excel to record my mileage, but have ran into a snags. My setup has odometer readings from start to finish, distance covered, gallons filled at the pump, and mileage.

My snags are that my programming for the mileage column has my first entry, then multiple #DIV/0! as there is no fuel entry. Any way to clean that up?

Also, I want to make a cumulative mileage counter, which will make an average of the mileage column while ignoring the zero entries. I thought I had it, but I ended up getting a cumulative MPG of 1.7 for entries 1-25.

Any MS Office savvy people out there that can fix my hack job?

As far as the multiple #DIV/0! is concerned, unless you temporarily make the color of the font white, it will always be there as long as the formula is in the cell. You could leave the cells with no entry blank until you have something on the line, and then just copy the formula manually when something is entered on the line. Re the cumulative mileage counter, I'll have to think on that one for a while......

[MichaelJ]

Use the "If...Then" function...

Something like: =IF(D16=0,"",C16/D16)

Where C16 is the mileage for this tank and D16 is the fuel taken.

The formula (in English) is:

If D16 equals 0, then display nothing, else display the result of C16 divided by D16.

You can now populate your spreadsheet with the formulae and they will only display a value when there is a value to be displayed.

[rfernatt]

Originally Posted by Feral_Collie

My snags are that my programming for the mileage column has my first entry, then multiple #DIV/0! as there is no fuel entry. Any way to clean that up?

Also, I want to make a cumulative mileage counter, which will make an average of the mileage column while ignoring the zero entries. I thought I had it, but I ended up getting a cumulative MPG of 1.7 for entries 1-25.

There's a few ways to do this. The previous suggestion on using the IF function is what I usually do, but if the cell is truly blank and does not contain a zero, then I would probably use =IF(A6="","",A6/B6). There is technically a difference between a zero value (0) and a null value ("").

You can just SUM the range to get the numerator for your average, but then you'll probably need to use the COUNT function to add up the number of actual entries (less the blank ones). So, if you had a range from A6:A100 and you have 60 actual entries and 35 blank cells, you could use COUNT(A6:A100) and it would return 60 for your denominator. Your average would then be returned by =SUM(A6:A100)/COUNT(A6:A100) if I'm understanding the question right.

[Feral_Collie]

Thanks for the help with the multiple #DIV/0! entries. As for cumulative mileage, I used the IF function:

=AVERAGE(IF(F2:F30<>0,F2:F30,""))

Column F is my tank by tank mileage, and I figured 29 entries should at least start me off. With this setup, it ignores any zero/nothing entry.

Thank you for all your inputs! I now have an impartial and accurate fuel log. Well, as accurate as the first click at a gas station will allow.