Facing problem for creating Formula-for Hired vehicles

Sir
we are hiring different vehicles on contract basis monthly, and we are recieving the bills on monthly basis

here i have to calculate total bill amount of the perticular vehicle, the data i will fill manually i.e is Start Time, Start reading, Closing time and closing KM these four line items i will fill rest the line items should come automatically (See in the excel attaced).

Thanks
Ronda

Base cost

=INDEX($H$5:$M$8,MATCH($J13,$F$5:$F$8,1),MATCH($G13,$H$3:$M$3,0)+MATCH($H13,$H$4:$I$4,0)-1)

Extra mileages cost

=INDEX($H$9:$M$9,,MATCH($G13,$H$3:$M$3,0)+MATCH($H13,$H$4:$I$4,0)-1)*$K13

Extra KM cost

=INDEX($H$10:$M$10,,MATCH($G13,$H$3:$M$3,0)+MATCH($H13,$H$4:$I$4,0)-1)*$L13


Extra hours

=MAX(0,J13-VLOOKUP($J13,$F$5:$F$8,1,1))

Extra Km

=MAX(0,$I13-VLOOKUP($I13,$G$5:$G$8,1,1))

sir

really appreciated your hardwork on this issue,

every thing is fine, one more thing is here the supplier will charge higher as per the slabs.

example:
if total travel KM 50 and total 12 hrs, then the supplier will charge only asper base rate i.e 12 hrs and 100KM slab..i.e say indica Non AC hence Rs.1123.20

if total travel KM 35 and total usage time 7 hrs, then the supplier will charge as per 8 hrs and 80 KM slab i.e 1098.24

same for all the vehicles

Thanks
Ronda

To be clear: should the hours ALWAYS be rounded up to the nearest of 2, 4, 8 or 12.

What are value for hours 1 through 15 (for example)

would they be ...
2, 2, 4, 4, 8, 8, 8, 8, 12, 12, 12 ,12, 12, 12,12

Aare extra hours allocated for hours below 12?

For example in the sheet;

INDIGO A/C for 11.25 hours was a base cost of 8/80 i.e.1747.20 Was this correct? should it have been 12/100?

Please clarify the rules as I made assumptions which are probably incorrect.

Sample calculations will help.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Sir, please excuse me, Since I am a new user and urgent, assure you i will follow next attempt.

Sir JohnTopley,

2/20 KM- This slab will change even 01 Hr or 01 KM extra.

Example: Total Travle Hrs: 3 and Total Km: 20, here 01 hr extra as per above slab, hence automatically the slab will go to 4 hrs 40 KM
And If the Total travel hrs: 1 and Total KM: 21, here 01 KM extra, hence the slab will be the 4 hrs 40 KM.

Remaining all are same


Note: Extra Hours and Extra KM will come into place only if more than 12 Hrs or 100 KM, If less it should be adjested in remaining/other slabs.

Q: INDIGO A/C for 11.25 hours was a base cost of 8/80 i.e.1747.20 Was this correct?
No,because the total hours is more than 8 hrs,hence the slab will be 12/100 km
Q:should it have been 12/100?
yes, because the time is morethan 8hrs

Thanks
Ronda

Please change thread title as requested as I will not be able to respond until this is done.

I have a solution.