Calculate value based on statuses in formula column

[hart (paige hart)]

Hi there!

I’m trying to create a formula that will calculate the commission on a file based on the loan amount and the values of some statuses on a board. Here are the parameters:

• If Comp Type is LPC and Lender is Trustmark, multiply loan amount by 0.02
• If Comp Type is LPC and Lender is UWM and Purpose is Refinance, multiply loan amount by 0.2
• If Comp Type is BPC, return 0
• Otherwise, multiply loan amount by 0.025

Here is what I have so far:

ROUND(IF((AND({Comp Type} = “LPC”,{Lender} = “Trustmark”),{Loan Amnt} * 0.02, IF(AND({Comp Type} = LPC,{Lender} = “UWM”,{Purpose} = “Refinance”),{Loan Amnt} * 0.02, IF({Comp Type} = “BPC”,0,{Loan Amnt} * 0.025)))),2)

Obviously this is not working! If anyone has any guidance or advice that would be appreciated. :~)

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

Hi @hart ,

Try:

ROUND(IF(AND({Comp Type} = "LPC",{Lender} = "Trustmark"),{Loan Amnt} * 0.02, IF(AND({Comp Type} = "LPC",{Lender} = "UWM",{Purpose} = "Refinance"),{Loan Amnt} * 0.02,IF({Comp Type} = "BPC",0,{Loan Amnt} * 0.025))),2)

---

What if we could break free of the Formula column? and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.

[WLane (Wally Lane)]

Hi Paige, I couldn’t spot anything, so I pasted it into Chat GPT and it just said that “there are a few syntax errors, such as misplaced parentheses and quotation marks.” Here’s the corrected code, seems to work.

ROUND(
  IF(
    AND({Comp Type} = "LPC", {Lender} = "Trustmark"), 
    {Loan Amnt} * 0.02, 
    IF(
      AND({Comp Type} = "LPC", {Lender} = "UWM", {Purpose} = "Refinance"), 
      {Loan Amnt} * 0.2, 
      IF(
        {Comp Type} = "BPC", 
        0, 
        {Loan Amnt} * 0.025
      )
    )
  ), 
  2
)

[hart (paige hart)]

this worked, thank you so much!!