Four Criteria Mileage Calculation

[DiegoJason]

Hi Folks

Hope you can help me with my calculation please?
Much appreciated for any assistance you can offer me on this.

[TABLE="width: 897"]
<tbody>[TR]
[TD="colspan: 3"]Enter Mileage Claim Here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]KM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Four Criteria Mileage Calculation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Band[/TD]
[TD]€/KM[/TD]
[TD]KM[/TD]
[TD]Range[/TD]
[TD]Distance KM[/TD]
[TD]€[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]€0.4479[/TD]
[TD]0[/TD]
[TD]to[/TD]
[TD]1500[/TD]
[TD]€671.85[/TD]
[TD]=MIN(A2,1500)*B7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]€0.8353[/TD]
[TD]1,501[/TD]
[TD]to[/TD]
[TD]5500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]€0.3221[/TD]
[TD]5,501[/TD]
[TD]to[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]€0.2585[/TD]
[TD]25,001[/TD]
[TD]to[/TD]
[TD]Infinity[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Claim[/TD]
[TD]€671.85[/TD]
[TD]=SUM(F7:F10)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Try these formulas:

Cell F8: =MIN(MAX(A$2-E7,0),E8-E7)*B8
Cell F9: =MIN(MAX(A$2-E8,0),E9-E8)*B9
Cell F10: =MAX(A$2-E9,0)*B10

 

[DiegoJason]

Hi Joe
That's really amazing, thank you very much i really appreciate you support.
Worked a treat

Best regards D

 

[Joe4]

You are welcome.

If you break each formula down, hopefully it makes sense.
If you have any questions about any part, please let me know.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	KM	Amount		Without table
2	5,501	4,213.37		4,213.37
3
4
5
6	KM	Rate	Delta
7	0	0.4479	0.4479
8	1,500	0.8853	0.4374	Please check rate.
9	5,500	0.3221	-0.5632
10	25,000	0.2585	-0.0636
2d

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(A2>KM),A2-KM,Delta)
D2	=SUMPRODUCT(--(A2>{0;1500;5500;25000}),A2-{0;1500;5500;25000},{0.4479;0.4374;-0.5632;-0.0636})
C7	=B7-N(B6)

Named Ranges
Name	Refers To	Cells
Delta	='2d'!$C$7:$C$10
KM	='2d'!$A$7:$A$10

 

[DiegoJason]

Hi Joe,
this worked really well thank you again.
I would like to find a resource (Maybe a PDF I can read) with math automation examples and explanations on these formula,
I have done mouse core and expert in the past but felt it didn’t do enough on the math and automation side.
Can you recommend something which can help aid my understanding?


Appreciate advice
Diego

 

[DiegoJason]

Thank you Dave

 

[Joe4]

I would like to find a resource (Maybe a PDF I can read) with math automation examples and explanations on these formula,

I don't know anything off the top of my head. Your best bet may be to start doing some Google searches.

Note that as far as Excel functions go, Google searches will return a lot of good information pretty easily (i.e. search on "Excel MAX function").