Calculating KM running and Mileage of vehicle

Nikhil Patki

New member
This is the format in which vehicle fuel entries are done
How do I calculate following using Excel Formulas
1. Total KM running.
2. Mileage of the vehicle

Note: -
Number of entries change each month
hence each month I've to calculate Mileage and Running manually every month at present.

DateVeh NoOp KmQty
07-10-23​
MH 14 CW 4109
12171​
32.87 Ltr​
17-10-23​
MH 14 CW 4109
12722​
20.00 Ltr​
21-10-23​
MH 14 CW 4109
12944​
65.33 Ltr​
21-10-23​
MH 14 CW 4109
12722​
20.00 Ltr​
 
This is the format in which vehicle fuel entries are done
How do I calculate following using Excel Formulas
1. Total KM running.
2. Mileage of the vehicle

Note: -
Number of entries change each month
hence each month I've to calculate Mileage and Running manually every month at present.

DateVeh NoOp KmQty
07-10-23​
MH 14 CW 4109
12171​
32.87 Ltr​
17-10-23​
MH 14 CW 4109
12722​
20.00 Ltr​
21-10-23​
MH 14 CW 4109
12944​
65.33 Ltr​
21-10-23​
MH 14 CW 4109
12722​
20.00 Ltr​
Hello Nikhil Patki

Assuming your data is in columns A to D (Date, Veh No, Op Km, Qty).

Follow these steps:

Step 1: Total KM Running
Select cell G2 => Insert the following formula => Hit Enter.
=MAX(C2:C5) - MIN(C2:C5)
Total KM Running.png

Step 2: Total Fuel Consumed
Select cell G3 => Insert the following formula => Press Enter.
Code:
=SUM(D2:D5)
Total Fuel Consumed.png

Step 3: Mileage of the Vehicle
Select cell G4 => Insert the following formula => Hit Enter.
Mileage of the Vehicle.png

I hope the idea will help you. Good luck

Regards
Lutfor Rahman Shimanto
 
it was really great to have your reply and support received.
I've learnt pretty much about excel.
but not yet applied it in routine work
also i'm in capable to use my logic in order to apply the knowledge in various scenarios
i just understand the things / working for which examples are explained or used in videos.
i really need to workmore hard and try to explore various formulas in different scenarios
so that i can use my logical ability and solve such silly queries on my own.
regarding mileage the last filled fuel entry is not meant to be used
as that fuel is still not utilised against the KM reading received for that respective filling.
also can above both formulas be made dynamic as every month the number of fuel filling entries are not same.
 
it was really great to have your reply and support received.
I've learnt pretty much about excel.
but not yet applied it in routine work
also i'm in capable to use my logic in order to apply the knowledge in various scenarios
i just understand the things / working for which examples are explained or used in videos.
i really need to workmore hard and try to explore various formulas in different scenarios
so that i can use my logical ability and solve such silly queries on my own.
regarding mileage the last filled fuel entry is not meant to be used
as that fuel is still not utilised against the KM reading received for that respective filling.
also can above both formulas be made dynamic as every month the number of fuel filling entries are not same.
Dear Nikhil Patki

I'm delighted to hear that you found our support helpful and that you've been learning a lot about Excel.

The formula presented here is a basic demonstration to solve that problem. Of course, these formulas can be enhanced to dynamically calculate dynamic fuel-filling entries for each month.

Regards
Lutfor Rahman Shimanto
 

Online statistics

Members online
0
Guests online
10
Total visitors
10

Forum statistics

Threads
311
Messages
1,378
Members
568
Latest member
WilliamHon
Top