Formula Question: If Status = True Multiply Total by Value

Hi Guys,

I am trying to create a Cash-Flow Management Board in which I have all contract totals of my sales,
I Invoice payments as progress payments so I want to build a formula column that when I change the status of 10%, 40%, 80% columns to “Paid” the formula multiplies the contract total by the desired amount, so I do not have to manually track how much is remaining of a job. Can someone help me with this?

Hi @tfuller, Tom,

I think I can help here. Let me know what column types you are using to set the 10%, 40% etc progress percentage. Is it a status column, or just a number column? Also, do you want the formula to show the amount as in “contract value” x “progress” or the opposite (what is currently open from the contract values).

@basdebruin Thank you so much for the reply.

I have a new scenario, this is for quoting my Jobs, I work as a Joiner and go of a lineal meter rate, I want to apply this to estimate a price but the have variations of that price change as I change the materials and hardware involved (that are in a status format), I have set up the lineal metre age conversion fine however I am struggling with the extension in having the status’ manipulate the price.

Hi Tom, @tfuller

Not sure I fully understand your scenario but in general the SWITCH statement is great for “converting” statuses to numbers, like:

SomeValue * SWITCH({StatusColumnName}, “Stat1”, 2, “Stat3”, 2.5, “Stat3”, 1.8, 1)

First argument is the column name of your status column
Next arguments are in pairs: status selected and the number (eg multiplier) this status represents
Last argument is the number to return as default (none of the mentioned statuses are selected)