Help! How can I add nested IF statements to create a formula for a step-up commission fee structure?

Platform discussions
, ,
1

I am trying to create a function in monday that automatically calculates a commission fee, however, the commission fees for “development” are structured in intervals instead of a flat fee for “PBSH” (see below):

Development Fee structure:

  • 6% fee from $0-$1M
  • 5% fee from $1M-$2M
  • 4% from $2M-$3M
  • 3% from $3M-$4M
  • 2% from $4M-5M
  • 1% at >$5M
  • Example: an {Ask Price} of $5.5M would calculate out to (1M*.06)+(1M*.05)+(1M*.04)+(1M*.03)+(1M*.02)+(500K*.01) = $205,000

This is my function of nested IF statements that I hoped would work but shows up as an illegal formula. Can anyone help me?

IF({Type}=“PBSH”,{Ask Price}{Rate}, (IF({Type}=“Development”, (IF({Ask Price}<1000000,{Ask Price}.06), IF(1000000<{Ask Price}<2000000,60000+(({Ask Price}-1000000).05), IF(2000000<{Ask Price}<3000000,110000+(({Ask Price}-2000000).04), IF(3000000<{Ask Price}<4000000,150000+(({Ask Price}-3000000).03), IF(4000000<{Ask Price}<50000000, 180000+(({Ask Price}-4000000).02), IF({Ask Price}>5000000, 200000+(({Ask Price}-5000000)*.01), 0)))))))

2

Hi Mitch,

You have more open parentheses than close parentheses. When you write formulas with so many nested functions, you may want to test it first: Check your monday.com formula.

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

3

Try this…

IF({askprice}<1000001,6% * {askprice},6% * 1000000+IF({askprice}<2000001,5% * ({askprice}-1000000),5% * 1000000+IF({askprice}<3000001,4% * ({askprice}-2000000),4% *1000000+IF({askprice}<4000001,3% * ({askprice}-3000000),3% * 1000000+IF({askprice}<5000001,2% * ({askprice}-4000000),2% * 1000000+IF({askprice}>5000000,1% * ({askprice}-5000000),0))))))

I had to put some extra spaces at the multiply so it shows correctly above, but you can take them out in the formula

1 Like
4

Mathhew,

Thank you for your help here. The function does not show any errors on the formula-check page, but it still shows “illegal formula” on my Monday table.

Let me know if you can think of anything else that I can change.

5

In my formula I had the column name as {askprice}… I think you have {Ask Price}…
I have also incorporated the {Type} query…

IF({Type}=“PBSH”,{Ask Price} * {Rate}, IF({Type}=“Development”, IF({Ask Price}<1000001,6% * {Ask Price},6% * 1000000+IF({Ask Price}<2000001,5% * ({Ask Price}-1000000),5% * 1000000+IF({Ask Price}<3000001,4% * ({Ask Price}-2000000),4% *1000000+IF({Ask Price}<4000001,3% * ({Ask Price}-3000000),3% * 1000000+IF({Ask Price}<5000001,2% * ({Ask Price}-4000000),2% * 1000000+IF({Ask Price}>5000000,1% * ({Ask Price}-5000000),0))))))))

image

1 Like
6

Try:

IF({Type}="PBSH", {Ask Price}*{Rate}, 
 IF({Type}="Development", 
  IF({Ask Price}<=1000000, {Ask Price}*0.06, 
   IF(AND({Ask Price}>1000000, {Ask Price}<=2000000), 60000+({Ask Price}-1000000)*0.05, 
    IF(AND({Ask Price}>2000000, {Ask Price}<=3000000), 110000+({Ask Price}-2000000)*0.04, 
     IF(AND({Ask Price}>3000000, {Ask Price}<=4000000), 150000+({Ask Price}-3000000)*0.03, 
      IF(AND({Ask Price}>4000000, {Ask Price}<=5000000), 180000+({Ask Price}-4000000)*0.02, 
       IF({Ask Price}>5000000, 200000+({Ask Price}-5000000)*0.01
       )
      )
     )
    )
   )
  ), 
  ""
 )
)

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.

1 Like
7

THIS WORKS!

Honestly can’t tell you why this worked and the other one didn’t but thanks to both of you!