Results 1 to 7 of 7

20031122, 06:52 #1
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20031122, 12:54 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Another Railroad Time Formula (1997)
How about this (with the if)
<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

20031122, 16:37 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20031122, 17:19 #4
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20031123, 00:57 #5
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20031123, 03:54 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20031123, 21:28 #7
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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