Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    2 Star Lounger
    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

  5. #5
    2 Star Lounger
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    2 Star Lounger
    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

Posting Permissions

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