mileage calc

[Trevor3007]

Hi,

Have tried & tried to sort this myself, but cannot & hoping Mr excel can provide the answer?

I am tryin to create a expenses calculation for mileage . But its not stright forward ( well for me that is).

For example, the total milage from start post code to end post code = 205miles.

I have to knock off the 1st 30 miles =175
The company allows 0.40 between 1 - 75miles = 75 x 0.40=£30
The comapny allows 0.20 for anything over 75 miles = 100 x 0.20 =£20

My issues are:-
I need to have something that when the total mileage meets the above , but also when the total mileage does not
For example, the total milage from start post code to end post code = 50 miles,

knock off the 1st 30 miles =20
The company allows 0.40 between 1 - 75miles =20 x 0.40 = £8

Also, as I will doing multi rows, if A2 ,a3, a4 etc is blank, than all the retrospective cells in that applicable row will be blank too ( just to keep things tidy)

I have treid to do this all on one row , so all I have to do is enter the total milage from start post code to end post code in A2..

Hoping this makes sense & thank you for helping me & taking up my issue.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B
1
2	205	50
3	50	8
4	20	0
5
6
Master

Cell Formulas
Range	Formula
B2:B5	B2	=IF(A2="","",IF(A2<30,0,(A2-30)*0.4-(MAX(0,A2-105)*0.2)))

 

[jasonb75]

See if this works

=SUMPRODUCT((A2>{0,30,105})*(A2-{0,30,105})*{0,0.4,-0.2})

 

[rlv01]

=MEDIAN(75,A1-30,0)*0.4 +MAX(A1-105,0)*0.2

=LET(Miles,A1,MEDIAN(75,Miles-30,0)*0.4 +MAX(Miles-105,0)*0.2)

 

[Trevor3007]

How about

+Fluff 1.xlsm
	A	B
1
2	205	50
3	50	8
4	20	0
5
6
Master

Cell Formulas
Range	Formula
B2:B5	B2	=IF(A2="","",IF(A2<30,0,(A2-30)*0.4-(MAX(0,A2-105)*0.2)))

How about

+Fluff 1.xlsm
	A	B
1
2	205	50
3	50	8
4	20	0
5
6
Master

Cell Formulas
Range	Formula
B2:B5	B2	=IF(A2="","",IF(A2<30,0,(A2-30)*0.4-(MAX(0,A2-105)*0.2)))

Hi Fluff
thanks for your help.....

Start PC-End PC Mileage	-30	1 -75 @0.40p	75> @0.20	Total Repaid
200	170

I think I must of not made myself clear... So sorry but I need it to look like the above .

 

[Trevor3007]

=MEDIAN(75,A1-30,0)*0.4 +MAX(A1-105,0)*0.2

=LET(Miles,A1,MEDIAN(75,Miles-30,0)*0.4 +MAX(Miles-105,0)*0.2)

Hi

thank you for your reply.

Start PC-End PC Mileage	-30	1 -75 @0.40p	75> @0.20	Total Repaid
200	170

I think I must of not made myself clear... So sorry but I need it to look like the above

 

[Trevor3007]

See if this works

=SUMPRODUCT((A2>{0,30,105})*(A2-{0,30,105})*{0,0.4,-0.2})

hi & thanks for your help.

Book1
	A	B	C	D	E
1	Start PC-End PC Mileage	-30	1 -75 @0.40p	75> @0.20	Total Repaid
2	200	170
3
4
5
6
Sheet1

Cell Formulas
Range	Formula
B2	B2	=A2-30
B4:B6	B4	=IF(A4="","",IF(A4<30,0,(A4-30)*0.4-(MAX(0,A4-105)*0.2)))

I think I must of not made myself clear... So sorry but I need it to look like the above .

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	Start PC-End PC Mileage	-30	1 -75 @0.40p	75> @0.20	Total Repaid
2	200	170	30	19	49
3
4
Lists

Cell Formulas
Range	Formula
B2:B4	B2	=IF(A2="","",A2-30)
C2:C4	C2	=IF(A2="","",MAX(0,MIN(B2,75))*0.4)
D2:D4	D2	=IF(A2="","",MAX(B2-75,0)*0.2)
E2:E4	E2	=IF(A2="","",SUM(C2:D2))

 

[rlv01]

Book1
	A	B	C	D	E
1	Start PC-End PC Mileage	-30	1 -75 @0.40p	75> @0.20	Total Repaid
2	200	170	30	19	49
Sheet1

Cell Formulas
Range	Formula
B2	B2	=MIN(A2-30)
C2	C2	=MEDIAN(75,A2-30,0)*0.4
D2	D2	=MAX(A2-105,0)*0.2
E2	E2	=MEDIAN(75,A2-30,0)*0.4 +MAX(A2-105,0)*0.2

 

[Trevor3007]

wahoooo.. thanks Fluff ?