1 Reply Latest reply on Apr 12, 2012 6:44 AM by Dillik

    Equation help



      Equation help


      In short, I have an equation (below) which subtacts the start time from the finish time, giving me an elapsed time.  I have an IF in the equation which will result in a blank result if there is no finish time to work with.  Instead of a blank, I would like to have the option of putting a DNF (did not finish), DNS (did not start/show) or DQ (disqualified) in the cell.  Is there a way to do such a thing?

      Thanks for any help or suggestions.


      If ( Finish Time ≠ 0 ; If ( Finish Time  =  0 ; Start Time ; Finish Time - Start Time + Penalty Time) ; "" )


        • 1. Re: Equation help

          You'd be better served using a Case function instead of nested Ifs, as such (making sure "Do not evaluate if all referenced fields are empty" is unchecked):

          Case (
          IsEmpty ( Start Time ); "DNS";
          IsEmpty ( Finish Time ); "DNF";
          ( Finish Time - Start Time ) + Penalty Time

          ...but the problem is that your result mixes time and text values.  If you set your calculation to output as text, you won't be able to perform finds and sorts on the result in a way that reflects actual time-based data.  If you set it to output as time, then the text results only show up as "?".  If it were me, I'd make a separate status field for the DNS, DNF, and DQ output (though I'm not sure what your criteria are for DQ).  The actual elapsed time calculation should only have a time result or blank itself out for invalid criteria:

          Case ( IsEmpty ( Start Time ) or IsEmpty ( Finish Time ); ""; ( Finish Time - Start Time ) + Penalty Time )

          (The above calc could use "If" instead of "Case" because it only has a single condition with true and false results, but if you use "Case" from the outset, you have the option of adding additional condition/result pairings freely.)  Anyway, the label field might be something like:

          Case ( IsEmpty ( Start Time ); "DNS"; IsEmpty ( Finish Time ); "DNF"; "" )

          (The fallback null result at the end is just there for clarity; leaving it out would produce the same result.)