averaging totals in excel for fuel mileage sheet

[raven54721]

How can I change the average amount at the end of a column so it does not round up to 2 decimal points? The totals do not come out to the right average because it is rounding. Help? Thanks

[JBeaucaire]

Change your SUM formula that is summing all the averages. Use a MAX formula to keep it from going above 100.

=MAX(1, SUM(M1:M10))

[raven54721]

thank you very much

[raven54721]

THIS formula did not work for my column...i will look into it further, thanks

[JBeaucaire]

You can always post a workbook so we can look at the problem with your data.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[raven54721]

FUEL MILEAGE #1 UNIT 6363 STOCK ECM.xls


Please see column for mileage that totals 5.830. It rounds up and if you take the miles total and divide by the gallons total and you get 5.91 but it doesnt match up with the 5.830 because that is an average and not actual. I would like to get the average total and the other total to be the same for checks and balances if you can see what I mean. Any suggestions are welcome and other tidbits as we are just starting to track this stuff for his truck. He bought the truck and with 8 payments left he had to get a new motor. Big investment so we want a good tracking system for mileage etc....thank you

[JBeaucaire]

Gotcha. Your formula in F13 should be simpler as

=AVERAGE(F4:F12)

But in my eyes, you're doing voodoo math if you average those values. And the reason why you've already spotted. It's wrong. The correct formula for the "MPG over time" is to do the math once at the end using the sums as the source data. The formula in F13 should be, IMO:

=C13/D13

[raven54721]

C13/D13 IS correct for the mpg from mileage divided by gallons, but it should work across then too, which column f is doing for each line 4-12...so I was hoping column f could come to the same amount of the answer to c13/d13. Hope you are understanding. 5.91 is the answer to c13/d13...so why cant i get the total of f to come out to 5.91 as it should? You folllow me? That way you could always check your entries to be correct if the answer to c13/d13 is 5.91 and f13/? is 5.91 you know you are doing it right. Thank you very much

[raven54721]

FUEL MILEAGE #1 UNIT 6363 STOCK ECM.xls

C13/D13 IS correct for the mpg from mileage divided by gallons, but it should work across then too, which column f is doing for each line 4-12...so I was hoping column f could come to the same amount of the answer to c13/d13. Hope you are understanding. 5.91 is the answer to c13/d13...so why cant i get the total of f to come out to 5.91 as it should? You folllow me? That way you could always check your entries to be correct if the answer to c13/d13 is 5.91 and f13/? is 5.91 you know you are doing it right. Thank you very much

[raven54721]

here is a second sheet with the same problem. See highlighted cells...these numbers should be matching. Please review and let me know more details if you can. Thank you again.FUEL MILEAGE #2 UNIT 6363 POWER ECM.xls

[JBeaucaire]

Let me repeat for emphasis:

=AVERAGE(F4:F12)

But in my eyes, you're doing voodoo math if you average those values. And the reason why you've already spotted. It's wrong.

OK? It doesn't matter how many different sets of data you try this on, it is the wrong formula to use. You cannot "average" a series of individual sets of MPG data and expect to get the correct answer for the WHOLE dataset in this manner. For some reason you are stuck thinking you "should" be able to, and that is a faulty conclusion.

You do not double check values like this. The math is simple, the answer is correct when the correct formula is used and does not require further checking.

=C13/D13 ....gives you the answer. Move on to the next project.

[raven54721]

Ok thank you very much. You are correct, as I was thinking that about the average. But again, I am thinking if he enters his data in columns c and d, and you total them and use your formula you can get the correct answer. I just was hoping there could be another column that would total them again and give the same answer so he can know he has entered correctly.

[JBeaucaire]

...so he can know he has entered correctly.

Know he has entered what correctly? As long as the miles traveled and gas purchased are entered on a daily basis, then the answers are the answers. Your table won't tell you he entered the correct numbers, only your receipts will confirm that.

Again, you're expecting this data to be "double checked" by the data itself. That's not going to happen with this column.