How to combine several IF statements in a calculated value field?

  • 15 August 2017
  • 3 replies
  • 14 views

Badge +6

This formula is working in a SharePoint calculated column but now I am needing to build a repeating section and I cannot get this same formula to work on a Nintex form within a calculated value field.  Note: The calculated value field/control is within a repeating section.

 

=IF([Pay Code]="700-Overtime",(([Hourly Rate]/2+[Hourly Rate])*[Hours Worked/Paid]),IF([Decimal Hours]>40,([Hourly Rate]*40)+([Decimal Hours]-40)*([Hourly Rate]/2+[Hourly Rate]),IF([Pay Code]="195-10% Differential",([Hourly Rate]*0.1)*[Decimal Hours],IF([Pay Code]="606-Night Differential",([Decimal Hours]*0.5),IF([Pay Code]="038-Freezer Pay",[Hours Worked/Paid]*1,[Decimal Hours]*[Hourly Rate])))))


3 replies

Userlevel 5
Badge +14

have you used it in forms just like you posted it here?

ie. do you reference list fields/form controls like "[Pay Code]"?

if so, this not going to work - you have to replace all list field references with Item properties resp. named controls, depending on what exactly you want to calculate.

Badge +6

We figured it out! We used a calculated value on the form and below is how we built the formula. The red references are controls within our repeating section.

 

IF(PayCode=="700-Overtime",(HourlyRate+(HourlyRate/2))*DecimalHours, 0) || IF(PayCode=="195-10% Differential",(DecimalHours*HourlyRate)+(DecimalHours*HourlyRate*.1), 0) || IF(PayCode=="606-Night Differential",(DecimalHours*0.5), 0) || IF(PayCode=="038-Freezer Pay",(HoursWorked*1), 0) || IF(DecimalHours>40,HourlyRate*40+DecimalHours-40*HourlyRate/2+HourlyRate, 0) || IF(PayCode=="001-Regular",(DecimalHours*HourlyRate), 0) || IF(PayCode=="035-Guarantee",(DecimalHours*HourlyRate), 0) || IF(PayCode=="040-Meeting",(DecimalHours*HourlyRate), 0) || IF(PayCode=="300-Sick",(DecimalHours*HourlyRate), 0) || IF(PayCode=="400-Vacation",(DecimalHours*HourlyRate), 0) || IF(PayCode=="409-Vacation in Lieu of Time Off",(DecimalHours*HourlyRate), 0) || IF(PayCode=="410-Holiday(1x)",(DecimalHours*HourlyRate), 0) || IF(PayCode=="972-PTO",(DecimalHours*HourlyRate), 0) || IF(PayCode=="973-PTO in lieu of time off",(DecimalHours*HourlyRate), 0)
 

Userlevel 5
Badge +14

I don't know if it's useful, but I noticed that you have some strange logic for when DecimalHours goes above 40. 

Because I'm not certain of your particular workplace's Overtime policies, I have no idea if this is even an issue, but due to the order of operations it would evaluate to a sum that is greater than what the typical 'Time and a half' for hours over 40 + the regular time would equal. 

if

HourlyRate = 1
DecimalHours = 50

HourlyRate * 40 + DecimalHours - 40 * HourlyRate / 2 + HourlyRate

1 * 40 + 50 - 40 * 1 / 2 + 1

(1 * 40) + 50 - ((40 * 1) / 2) + 1

40 + 50 - 40 * 1 / 2 + 1

40 + 50 - 20 + 1

90 - 20 + 1

70 + 1

71
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

As opposed to

(HourlyRate * 40) + ((HourlyRate  / 2 + HourlyRate) * (DecimalHours - 40))

(1 * 40) + ((1 / 2 + 1) * (50 - 40))

40 + ((.5 + 1) * (10))

40 + (1.5 * 10)

40 + 15

55 ‍‍‍‍‍‍‍‍‍‍‍

Additionally (and totally unnecessarily, it should be stressed), I have rewritten your formula in two different ways that might allow you to better maintain it in the event that you all add more PayCodes in the future. The first way is more or less a scaled back version of what you already have, where you'd have to replace the Named Control values shown here in this code with your actual named controls in Nintex Forms (the Red 'links' that is): 

If(DecimalHours > 40, (HourlyRate * 40) + ((HourlyRate / 2 + HourlyRate) * (DecimalHours - 40)),
  If(PayCode === "700-Overtime", ((HourlyRate + (HourlyRate / 2)) * DecimalHours),
    If(PayCode === "195-10% Differential", (DecimalHours * HourlyRate) + (DecimalHours * HourlyRate * .1),
      If(PayCode === "606-Night Differential", (DecimalHours * 0.5),
        If(PayCode === "038-Freezer Pay", (HoursWorked * 1),
          If(["001-Regular",
              "035-Guarantee",
              "040-Meeting",
              "300-Sick",
              "400-Vacation",
              "409-Vacation in Lieu of Time Off",
              "410-Holiday(1x)",
              "972-PTO",
              "973-PTO in lieu of time off"].indexOf(PayCode) > -1, (DecimalHours * HourlyRate), 0)
        )
      )
    )
  )
)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

While this second formula is a bit more strange looking (an... probably overkill...), but requires that you only put your actual Named Controls in at the very end (As In: ...}(PayCode, HourlyRate, DecimalHours, HoursWorked)) ), as it creates placeholders (variables) for them in an internal function that will return the value. I tend to write things this way because it's easier to just copy and paste without actually needing to go through and replace EVERY value that should be a Named Control with the proper link. 

(function(payCode, hourlyRate, decimalHours, hoursWorked) {
    var returnValue = 0;

    if (decimalHours > 40) {
      returnValue = (hourlyRate * 40) + ((hourlyRate / 2 + hourlyRate) * (decimalHours - 40));
    } else {
      switch (payCode) {
        case "700-Overtime":
          returnValue = (hourlyRate + (hourlyRate / 2)) * decimalHours;
          break;
        case "195-10% Differential":
          returnValue = (decimalHours * hourlyRate) + (decimalHours * hourlyRate * .1);
          break;
        case "606-Night Differential":
          returnValue = (decimalHours * 0.5);
          break;
        case "038-Freezer Pay":
          returnValue = (hoursWorked * 1);
          break;
        case "001-Regular":
        case "035-Guarantee":
        case "040-Meeting":
        case "300-Sick":
        case "400-Vacation":
        case "409-Vacation in Lieu of Time Off":
        case "410-Holiday(1x)":
        case "972-PTO":
        case "973-PTO in lieu of time off":
          returnValue = (decimalHours * hourlyRate);
          break;
      }
    }
    return returnValue;
  }(PayCode, HourlyRate, DecimalHours, HoursWorked))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You'll notice that both of my formulas are not using your original math for the DecimalHours > 40, but instead something else. That could be wrong and you could totally not want it that way. No matter, I just wanted to point that out! 


Reply