Calculating KM running and Mileage of vehicle

[Nikhil Patki]

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.

Date	Veh No	Op Km	Qty
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​

 

[Lutfor Rahman Shimanto]

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.

Date	Veh No	Op Km	Qty
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)

​

Step 2: Total Fuel Consumed
Select cell G3 => Insert the following formula => Press Enter.

=SUM(D2:D5)

​

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

=G2/G3

​

I hope the idea will help you. Good luck

Regards
Lutfor Rahman Shimanto

 

[Nikhil Patki]

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.

 

[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.

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