Hi all, im trying to make a formula column that calculates commission, depending on the capital and product chosen. Product has 2 choices Portfolio and Moneybox and they are set up in a status column, what i have at the moment is this.
It works but at half, doesnt changes when Capital is lower than 499999 and also i dont know how to set it for when Producto is Moneybox as it wont use a > or < Capital
@gibo recently did something similar to this during a monday.com apps hackathon!
They might need to do a bit more custom work to allow for the IF statements, but they will be able to help you out! I am sure Gilberto will reach out to you when he comes online to discuss this further with you.
Hi @DiegoDelRio,
Omnidea is actually working on a custom Monday App that allows to put the result of a formula inside a Number or Date column in order to trigger automations or other formula updates.
I’m not sure I completely understood your use case (the formula you’ve pasted here contains syntax errors) and possibly a different approach can be used even without requiring our App.
Feel free to share additional details here or dropping an email at info@omnidea.it
Just to let you know that formula result can be displayed in column and charts, but not used in automations or as input in further calculations.
This is why our app mentioned by @mitchell.hudson and @gibo is now in development.
Hello and thank you for your answer, a bit more explained would be that i have 2 kind of products: Portafolio and Moneybox, both in a status column so you can chose one of them, and in another column the Capital, which can be typed. What im looking for is that in the column formula you can set it so when you chose Portafolio and Capital is bigger than 500,000, Capital multiplies for 0.00375, then if you choose Portafolio and Capital is lower than 500,000, Capital multiplies for 0.0075, and finally if you choose Moneybox, Capital doesnt matter here if bigger ir lower, it multiplies for 0.0035, i dont know if thats possible as the limitations ive found with formula column, its like somekind of thing you can do with Python or Java with the If, Else if or Elif. I hope i clear up my oint a little more for you to understand me. Thank you again
Hello and thx for your answer, i tried to put this formula in my formula column but its not working, like not taking some column or a data, so the result appearing is the ! I have 3 columns, Product as a staus column (Portafolio and Moneybox) , Capital as a number column and my Formula column, its really close to what you put me there, except for the Moneybox part and that is not working idk what could be the reason
Hey, thx for your answer, yeah what im doing is not for automation, just for the column to do the correct operation when case 1, case 2 or case 3 happens. Oh for extra information i already tried with SWITCH too, but didnt worked in this case for the datas needed.
Formulas can be tricky here because you have to get it EXACTLY right for it to work.
If I understand your situation correctly, the formula text should work for you without any changes except the “0.9999” at the end. The formula text I gave you works like this:
If both “{Capital}>=500000” and "{Producto}=“Portafolio” are true, the value returned is {Capital} x 0.00375,
if the previous conditions are NOT both true but {Producto}=“Portafolio”, the value returned is {Capital} x 0.0075,
if all previous conditions were not met, the value returned is {Capital} x 0.9999))
So, even though “Moneybox” is not mentioned, if {Producto} is not equal to “Portafolio” it is assumed to be “Moneybox”. And, the returned value will be {Capital} x 0.9999.
If that doesn’t make sense or I’m misunderstanding, let me know and I’ll see if I can help.
Hello and thank you again @JCorrell for your help, i think i got your point as how it works, but for some reason its not working, im copying exaclty the formula you type there but keeps showing me the !
Also i tried it in a clean template just with this 3 columns, but still something happening, is it working for you with this 3 columns, or maybe im doing something wrong.
Pretty much this one works fine with what im needing, the one from @JCorrell wasnt far from working, i just dont know what was happening that wasnt working for me.
But thank you all for your support and help with this, might keep doing tests with this formulas, hope you get your app out soon, as it sounds awesome.
Will probably see me more often around here, as many questions i stil have about other topics and others that will come with time.
