Tracking Totals In A Single Cell

**GuyWhoSucks@Excel** · 08-05-2013, 06:47 AM

Alright everyone, I'm deployed and got tasked to creat an excel spreadsheet to track total miles, hours, and fuel on our vehicles. The idea is that we can enter the info along with a date in one line when we fuel up, then go to the next line at the next fuel stop. Then have the total number of miles/hours for the month total up in one cell each at the bottom (i.e. 2000 miles to 2100 miles, to 2200 miles would total to 200 at the bottom). I want to keep it "Army Proof" (Go Air Force, no offence to any Army on the forum), so I'll need to figure out how to lock the cells with the formulas too. Thanks!

Date Miles Hours Litres
1-Aug-13 22430 734 41.4
4-Aug-13 22462 745 59.4
5-Aug-13 22474 751 26.8

Totals 44 17 127.6

**Ron Coderre** · 08-05-2013, 07:05 AM

In those circumstance, I find it useful to put the totals at the TOP of the data. That way there's no chance of someone overwriting or deleting the row when entering data.
If you give us some sample data, we can tailor a possible solution for you.

Is that something you can work with?

**GuyWhoSucks@Excel** · 08-05-2013, 07:15 AM

I can track the totals at the top, on the side, pretty much where ever. Update: I'm going to need to track MPG and Gal/Hr as well. Still trying to get those to auto update as well when the info is entered under miles/hours/liters columns, but I keep getting "#DIV/0!" when I try to drag the formula through to the cells below. Which I get because it's trying to divide by 0, but I'm sure there's a way to work it I'm just not familiar with. I did get the liters and gallons to convert and total at the bottom without too much trouble.Spreadsheet.jpg

**Ron Coderre** · 08-05-2013, 07:32 AM

Here's an approach to try...
For total miles driven...where the miles are entered in Col_B, beginning in cell B2
=MAX(B:B)-Index(B:B,2)

I'd use MIN in the second part, but somebody might enter a zero someplace and ruin the results.

Does that help?

**kvsrinivasamurthy** · 08-05-2013, 07:39 AM

Pl see attached file.

**GuyWhoSucks@Excel** · 08-05-2013, 08:14 AM

Thanks for the help. I did end up moving the totals off to the side per Ron's advice, but both solutions worked like a champ. Ron, were you referencing using MIN to calculate the MPG, etc. issues? I've attached a copy of what the workbook looks like now for reference. Getting the MPG and Gal/Hr is still getting the best of me as well as keeping those two averaged in the last column. Also, are there tutorials on this site that can show me the things you're recommending?