1. ## Another Railroad Time Formula (1997)

I want to do the following:

If S3<=100, Multiply AT3 (a time format)*12.5 + (AU3 (time format) +AV3 (time format)) * 18.75, If S3>=101, Multiply AT3 (a time format)*16.25+ (AU3 (time format) +AV3 (time format)) * 24.375.

I tried the following formula without results. I kept getting a formula error.

=IF(S3<=100,(S3)*((HOUR(AT3)+MINUTE(AT3)*12.5)+((( HOUR(AU3)+MINUTE(AU3))+((HOUR(AV3)+MINUTE(AV3))*18 .75),IF(S3>=101,(S3)*((HOUR(AT3)+MINUTE(AT3)*12.5) +(((HOUR(AU3)+MINUTE(AU3))+((HOUR(AV3)+MINUTE(AV3) )*24.375)))))))))

Thanks

Larry

2. ## Re: Another Railroad Time Formula (1997)

<pre>=IF(S3<=100,(AT3*12.5+(AU3+AV3)*18.75),AT3*16 .25+(AU3+AV3)*24.375)</pre>

or using Boolean:
<pre>=(S3<=100)*(AT3*12.5+(AU3+AV3)*18.75) + (S3>=101)*(AT3*16.25+(AU3+AV3)*24.375)</pre>

I am not sure why you did all the adding hours and minutes together and why you are adding times.
If you have a time of 5:30 in for example AU3, doing:
Hour (AU3) + minute(AU3) = 5 + 30 = 35. I have no idea what this number is supposed to mean

Steve

3. ## Re: Another Railroad Time Formula (1997)

Another thing to mention:
If those times are excel times their values are actually in Days not hours (it does no matter what the display shows). So if you numbers are something like "Dollars/ hour) and you multiply by the "time" (in days) you need to multiply this value by 24 (hours/day). Thus maybe this is more what you are after:

<pre>=IF(S3<=100,(AT3*12.5+(AU3+AV3)*18.75),AT3*16 .25+(AU3+AV3)*24.375)*24</pre>

<pre>(S3<=100)*(AT3*12.5+(AU3+AV3)*18.75) + (S3>=101)*(AT3*16.25+(AU3+AV3)*24.375)</pre>

or even:
<pre>=24*(At3 +(Au3+AV3)*1.5)*((s3<=100)*12.5 + (S3>=101)*16.25)</pre>

Steve

4. ## Re: Another Railroad Time Formula (1997)

As usual, fine job.

The reason that I am taking a time and replacing it with a number is that the Federal Government reporting papers still dictate that we show total miles claimed (archaic, in reality ALL reporting should be in time only). So what I have done here is to take the basic run and multiply it by 12.5 (if routing code =<100), then take over time + over the hours of service time and multiply by 18.75 to get a total amount of miles claimed, or

take the basic run and multiply it by 16.25 (if routing code =>101), then take over time + over the hours of service time and multiply by 24.375 to get a total amount of miles claimed.

Thanks

Larry

5. ## Re: Another Railroad Time Formula (1997)

Sorry, but I need to modify the formula. I did not think this one out all the way. Your formula worked perfectly, but did not suit the purpose.

If S3 <=100, I need to take cell BA3 (miles run), [then AS3 (over time) + AT3 (over the hours of service time) and multiply by 18.75] and add to BA3 to get a total amount of miles claimed, or If S3 >=101, I need to take cell BA3 (miles run), then add [AS3 (over time) + AT3 (over the hours of service time) and multiply by 24.375] and add to BA3 to get a total amount of miles claimed.

Thanks

Larry

6. ## Re: Another Railroad Time Formula (1997)

Not sure what you want. It sounds like you are asking for:

<pre>=BA3 + 24*(AS3 + AT3)*((s3<=100)*18.75 + (S3>=101)*24.375)</pre>

Which is like the last one only the BA3 replaces the the 2 possible values: AT3*24 * (either 12.5 or 16.25).

Do you prefer these boolean formulas (that is what your original post used) or would you like something with an IF (I find the IFs are more understandable by people, though the boolean are more efficient calculations: IFs are memory hogs):

<pre>=BA3 + 24*(AS3 + AT3)*If(S3<=100, 18.75,24.375)</pre>

Steve

7. ## Re: Another Railroad Time Formula (1997)

Steve, I used the Boolean formula and rellaced with BA3. I had the *24 transposed with *42. Working too many hours, and have a tendency to transpose when overly tired. Have put the project aside for a few to regroup.

Thanks

Larry

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•