Calculate Mileage

[rsikin]

Good Evening

I am trying to write a macro for our travel statement. I am trying to
use sumproduct. Any help would be gratefull. Thanks. I also have
attached a sample sheet.

1st Scenario
If an employee is out of the office and less than 24 hours and less
than or equal to 125 miles then .485 time miles or if more than 125
miles than $60.625 plus .15 times miles greater than 125.

2nd Scenario
If an employee is out of the office and greater than or equal to 24
hours but less than 48 and less than or equal to 250 miles then .485
time miles or if more than 250 miles than $121.25 plus .15 times miles
greater than 250.

3rd Scenario
If an employee is out of the office and greater than or equal to 48
hours but less than 72 and less than or equal to 375 miles then .485
time miles or if more than 375 miles than $181.88 plus .15 times miles
greater than 375.

4th Scenario
If an employee is out of the office and greater than or equal to 72
hours but less than 96 and less than or equal to 500 miles then .485
time miles or if more than 500 miles than $242.50 plus .15 times miles
greater than 500.

5th Scenario & Final
If an employee is out of the office and greater than or equal to 96
hours but less than 120 and less than or equal to 625 miles then .485
time miles or if more than 625 miles than $303.13 plus .15 times miles
greater than 625.


+-------------------------------------------------------------------+
|Filename: Mileage.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3868 |
+-------------------------------------------------------------------+

 

[HiArt]

Hi rsikin,

in the Mileage column you need to add the following formula:

Code
-------------------
=IF(F7-C7<1,IF(G7<=125,G7*0.485,60.625+(0.15*(G7-125))),IF(AND(F7-C7>=1,F7-C7<2),IF(G7<=250,G7*0.485,121.25+(0.15*(G7-250))),IF(AND(F7-C7>=2,F7-C7<3),IF(G7<=375,G7*0.485,181.88+(0.15*(G7-375))),IF(AND(F7-C7>=3,F7-C7<4),IF(G7<=500,G7*0.485,242.5+(0.15*(G7-500))),IF(AND(F7-C7>=4,F7-C7<5),IF(G7<=625,G7*0.485,303.13+(0.15*(G7-625))),"Error"))))
-------------------


This is a bit less daunting if you break this down:

Code
-------------------
=IF( F5-C5<1 ,IF(G5<=125,G5*0.485,60.625+(0.15*(G5-125))),
IF(AND(F5-C5>=1,F5-C5<2),IF(G5<=250,G5*0.485,121.25+(0.15*(G5-250))),
IF(AND(F5-C5>=2,F5-C5<3),IF(G5<=375,G5*0.485,181.88+(0.15*(G5-375))),
IF(AND(F5-C5>=3,F5-C5<4),IF(G5<=500,G5*0.485,242.50+(0.15*(G5-500))),
IF(AND(F5-C5>=4,F5-C5<5),IF(G5<=625,G5*0.485,303.13+(0.15*(G5-625))),"Error"))))
-------------------


Now you can see that there is an IF statement for each scenario.

The first IF determines the number of days out of office followed b
another IF to calculate the expenses using the requirements yo
provided.

Lastly, "Error" is there in case the number of days exceeds 5.

HTH

Ar

 

[rsikin]

Good Morning HiArt

That is awesome! It is working great.

Thank you very much!!
Robert

 

[Roger Govier]

Hi

As an alternative to the long IF statement solution you have been given, you
could consider the following.

Your described scenario simplifies down to the fact that the payments are
..485 per mile up to a defined mileage, plus .15 per mile for all miles in
excess.

Time out of office has no bearing on payment, other than to determine the
mileage that attracts the higher rate.

The defined mileage is a simple function of up to 24 hours away from office,
milage equals 125, + 125 miles for each additional complete 24 hour period.

On your model, you are adding hours to dates in columns C and F (you don't
need =+A5+B5 incidentally, just =A5+B5 will suffice). However, you then
don't use the time when calculating days, as you are taking away column A
from column D, whereas it should be taking column C from column F.
Again, the DAYS360() function is incorrect in this scenario as it is based
upon equal month lengths of 30 days (it s normally only used for financial
bond calculations). Just F5-C5 will give the answer.

Now, your column H is headed days which instead you could turn into Miles.
In cell H5 enter
=MAX(125,(INT(F5-C5)*24)/24*125)
which will calculate the number of miles up to which the higher rate is paid.

In Cell I5 enter
=MIN(G5,H5)*0.485+MAX(0,G5-H5)*0.15
which will give the correct payment.

What this is saying is take the Minimum of Actual Mileage and allowed
mileage for that time period and multiply by .485, and add to that the
Maximum of 0 or Actual Mileage minus Allowed mileage times .15.
If the miles covered is less than thatv allowed for the time duration, then
the miles to be paid at lower rate would be negative. The 0 value prevents
that coming in to play.


Regards

Roger Govier

 

[rsikin]

Thanks Roger Govier

That was great, I found on the web the function max, min and I have not
ever used it.

Again Thank you very much!

 

[Roger Govier]

You're very welcome. Thanks for the feedback.
I think you will find it easier to maintain than using all the IF's.

Regards

Roger Govier