How do I use formula for calculate commission based on trip count based.

[riyajusg]

We have provide delivery/transport services, also we follow the excel sheet for daily trip details. End of the day, we have to pay commission to drivers based on Trip count.

We have some commission slaps based transport types like as follows,

we should calculate Commission report like as below,

Please help us to how to do it and how the formula can use to solve it..

Thanks in advance

 

[mart37]

For the Car Commision you can use VLOOKUP. Set the 4th argument to TRUE and you can find values between.
Change the first column in:
1
7
8
11
For the Bike Commission you can use INDEX and MATCH. With MATCH you can searc two fileds in two rows (or columns) with the symbol &
Change the titles in unigue titles.
Example:
first row: area1
second row: weekday
(third row: own bike)

 

[mart37]

An example with the hours table and the commission table have a different structure (columntitles).
When this two tables have the same structure then you can simple refer to the hours with =B4
In two different tables you have to search for the hours.

Map2
	A	B	C	D	E	F	G	H	I	J
1		area 1	area 2	area 1	area 2	area 1	area 2	area 1	area 2
2		weekday	weekday	weekday	weekday	weekend	weekend	weekend	weekend
3		own bike	own bike	company bike	company bike	own bike	own bike	company bike	company bike	car
4	driver 1	4		5		6		3		2
5	driver 2		2		5					7
6	driver 3	1					8		10
7
8	Kolom1	weekday	weekday	weekend	weekend		car	each
9		own bike	company bike	own bike	company bike		1	5
10	area 1	10	20	30	40		7	7,142857143
11	area 2	50	60	70	80		8	3
12	area 3	40	50	60	40		11	7
13
14		area 1	area 1	area 1	area 1	area 2	area 2	area 2	area 2
15		weekday	weekday	weekend	weekend	weekday	weekday	weekend	weekend
16		own bike	company bike	own bike	company bike	own bike	company bike	own bike	company bike	car
17	driver 1	40	100	180	120	0	0	0	0	10
18	driver 2	0	0	0	0	100	300	0	0	50
19	driver 3	10	0	0	0	0	0	560	800	0
Sheet1

Cell Formulas
Range	Formula
H10	H10	=50/7
B17:I19	B17	=INDEX($B$4:$I$6,MATCH($A17,$A$4:$A$6,0),MATCH(B$14&B$15&B$16,INDEX($B$1:$I$1&$B$2:$I$2&$B$3:$I$3,0),0))*INDEX($B$10:$E$12,MATCH(B$14,$A$10:$A$12,0),MATCH(B$15&B$16,INDEX($B$8:$E$8&$B$9:$E$9,0),0))
J17:J19	J17	=IFERROR(VLOOKUP($A17,$A$4:$J$6,10,FALSE)*VLOOKUP(VLOOKUP($A17,$A$4:$J$6,10,FALSE),$G$9:$H$12,2,TRUE),0)

 

[riyajusg]

An example with the hours table and the commission table have a different structure (columntitles).
When this two tables have the same structure then you can simple refer to the hours with =B4
In two different tables you have to search for the hours.

Map2
	A	B	C	D	E	F	G	H	I	J
1		area 1	area 2	area 1	area 2	area 1	area 2	area 1	area 2
2		weekday	weekday	weekday	weekday	weekend	weekend	weekend	weekend
3		own bike	own bike	company bike	company bike	own bike	own bike	company bike	company bike	car
4	driver 1	4		5		6		3		2
5	driver 2		2		5					7
6	driver 3	1					8		10
7
8	Kolom1	weekday	weekday	weekend	weekend		car	each
9		own bike	company bike	own bike	company bike		1	5
10	area 1	10	20	30	40		7	7,142857143
11	area 2	50	60	70	80		8	3
12	area 3	40	50	60	40		11	7
13
14		area 1	area 1	area 1	area 1	area 2	area 2	area 2	area 2
15		weekday	weekday	weekend	weekend	weekday	weekday	weekend	weekend
16		own bike	company bike	own bike	company bike	own bike	company bike	own bike	company bike	car
17	driver 1	40	100	180	120	0	0	0	0	10
18	driver 2	0	0	0	0	100	300	0	0	50
19	driver 3	10	0	0	0	0	0	560	800	0
Sheet1

Cell Formulas
Range	Formula
H10	H10	=50/7
B17:I19	B17	=INDEX($B$4:$I$6,MATCH($A17,$A$4:$A$6,0),MATCH(B$14&B$15&B$16,INDEX($B$1:$I$1&$B$2:$I$2&$B$3:$I$3,0),0))*INDEX($B$10:$E$12,MATCH(B$14,$A$10:$A$12,0),MATCH(B$15&B$16,INDEX($B$8:$E$8&$B$9:$E$9,0),0))
J17:J19	J17	=IFERROR(VLOOKUP($A17,$A$4:$J$6,10,FALSE)*VLOOKUP(VLOOKUP($A17,$A$4:$J$6,10,FALSE),$G$9:$H$12,2,TRUE),0)

Hi @mart37

Thank you for your great assistance, As I have encounter from your calculation formula shows wrong value, If Trip count above 11..
Hereby I have attached calculation method for your better view,

pls help me fix this issue..

Thanks in advance

 

[mart37]

a simple solution:

Map2
	A	B	C	D	E	F	G	H	I	J
14		area 1	area 1	area 1	area 1	area 2	area 2	area 2	area 2
15		weekday	weekday	weekend	weekend	weekday	weekday	weekend	weekend
16		own bike	company bike	own bike	company bike	own bike	company bike	own bike	company bike	car
17	driver 1	40	100	180	120	0	0	0	0	10
18	driver 2	0	0	0	0	100	300	0	0	80
19	driver 3	10	0	0	0	0	0	560	800	103
20
21
22	car trip	commission
23	1	5
24	2	10
25	3	15
26	4	20
27	5	25
28	6	30
29	7	80
30	8	83
31	9	86
32	10	89
33	11	96
34	12	103
35	13	110
36	14	117
37	15	124
Sheet1

Cell Formulas
Range	Formula
B17:I19	B17	=INDEX($B$4:$I$6,MATCH($A17,$A$4:$A$6,0),MATCH(B$14&B$15&B$16,INDEX($B$1:$I$1&$B$2:$I$2&$B$3:$I$3,0),0))*INDEX($B$10:$E$12,MATCH(B$14,$A$10:$A$12,0),MATCH(B$15&B$16,INDEX($B$8:$E$8&$B$9:$E$9,0),0))
J17:J19	J17	=IFERROR(VLOOKUP(VLOOKUP($A17,$A$4:$J$6,10,FALSE),$A$23:$B$37,2,TRUE),0)

 

[riyajusg]

I have solved above issue using following method,

Function Used
MEDIAN
MIN
MAX

Methods
Trip Count is between = 1 to 6 --> Min Function used
Trip Count is = 7 --> Fixed Value
Trip Count is between 8 to 10 --> Fixed value + Median()
Trip Count is 11 and above --> Fixed Value+ Median()+Max()

We got exact what we required using above method,

However, Thanks for your great support and assistance.