Skip to main content

I’m working on creating a Table that lists Expenses.


I’m currently using the following formula to populate a Title for the first column in the table based on columns: Vendor, Frequency, Price, Per Member Price?, Users and Total Due. The “Per Member Price?” is a checkbox field and the Total Due column is calculated by multiplying Price x Users.


CONCATENATE({Vendor}, " ($", {Total Due}, Frequency, IF({Per Member Price?}, " per member", ""), ")")


This results in data displaying like this:


App 1 ($52/mo.) if the “Per Member Price?” is not checked OR App 1 ($52/mo. per member) if the “Per Member Price?” is checked.


The problem is, is that regardless of if it’s supposed to be per user or not, the Total Due is always present vs conditionally showing the per member price as defined in the Price column.


I THINK I might need a nested condition here within the CONCATENATE… something like:


CONCATENATE({Vendor}, IF( {Per Member Price?} = 0, "$", {Total Due}, Frequency, IF({Per Member Price?}, "$", Price, Frequency, " per member", ""), ) )


That last formula doesn’t work/is broken so I’m looking to see if anyone who’s got more experience with formula’s can help me sort it out.


Ideally the end result would be the cell displaying it’s data like this:


App 1 ($52/mo.) if the “Per Member Price?” is not checked OR App 1 ($13/mo. per member) if the “Per Member Price?” is checked.


Any help/tips would be appreciated. 🙂

for starters the last comma needs to come out.


To concatenate within an if statement I use the ‘&’ operator instead of concatenate()

then wrap the entire conditional in a concatenate() formula

Thanks @Rebecca_Elam for pointing out the extra comma. Having another pair of eyes definitely helps with some of these formula’s.


I ended up reversing the formula to CONCATENATE within the IF statement vs. trying to use an IF statement within CONCATENATE; which is what I think was being referred to above.


The end result is the following formula (tested and working) for those trying to do something similar:


IF(

	OR({Per Member Price?} = 0, Users = 0),

	CONCATENATE(Vendor," $", {Total Due}, Frequency),

	IF(

		AND({Per Member Price?}, Users > 0),

		CONCATENATE(Vendor," $", Price, "/user", Frequency )

	)

)

Note: I did include an extra OR condition in the above formula to check if the value in the “Users” field is set to zero. If you don’t do this, and the “Per Member Price?” column is checked but Users are set to 0 the title will be blank.


Switch vs. IF - I was thinking that I might be able to use a SWITCH statement here vs. IF, since I’m calling {Per Member Price?} multiple times; however, my attempts at that just resulted in a blank cell.


If anyone has an example of how to get that working with SWITCH I’d love to see it for reference. 🙂

It is much easier to write formulas like this if you use a multi-line syntax.


The original formulas seem to have too many parameters per IF statement.

Perhaps you are mixing up what belongs in the IF versus the CONCATENATE.


Do you want something like this?


CONCATENATE(

    {Vendor}, 

    " ($", 

    IF( {Per Member Price?}, {Price}, {Total Due}),

    {Frequency},

    IF( {Per Member Price?}, " per member"),

    ")"

)

It is much easier to write formulas like this if you use a multi-line syntax.


The original formulas seem to have too many parameters per IF statement.

Perhaps you are mixing up what belongs in the IF versus the CONCATENATE.


Do you want something like this?


CONCATENATE(

    {Vendor}, 

    " ($", 

    IF( {Per Member Price?}, {Price}, {Total Due}),

    {Frequency},

    IF( {Per Member Price?}, " per member"),

    ")"

)

Hey @kuovonne,



Perhaps you are mixing up what belongs in the IF versus the CONCATENATE .



I probably was, I’m not the best at writing formula’s. :grinning_face_with_sweat:


I just tested your formula and that was essentially what I was trying to do with the IF within CONCATENATE but for w/e reason my silly brain couldn’t make that work originally. Thanks for showing me that formula!


Prior to your reply I ended up wrapping CONCATENATE within the IF and that seems to also work. Below is the formula I ended up with, although I did adjust the text just a little bit from my original request - to put the “per members” aka “/users” before the Frequency:


IF(

	OR({Per Member Price?} = 0, Users = 0),

	CONCATENATE(Vendor," $", {Total Due}, Frequency),

	IF(

		AND({Per Member Price?}, Users > 0),

		CONCATENATE(Vendor," $", Price, "/user", Frequency )

	)

)


It is much easier to write formulas like this if you use a multi-line syntax.



I can see definitely see why it’s much easier to read; I adjusted my formula to be multi-line. 🙂

Hey @kuovonne,



Perhaps you are mixing up what belongs in the IF versus the CONCATENATE .



I probably was, I’m not the best at writing formula’s. :grinning_face_with_sweat:


I just tested your formula and that was essentially what I was trying to do with the IF within CONCATENATE but for w/e reason my silly brain couldn’t make that work originally. Thanks for showing me that formula!


Prior to your reply I ended up wrapping CONCATENATE within the IF and that seems to also work. Below is the formula I ended up with, although I did adjust the text just a little bit from my original request - to put the “per members” aka “/users” before the Frequency:


IF(

	OR({Per Member Price?} = 0, Users = 0),

	CONCATENATE(Vendor," $", {Total Due}, Frequency),

	IF(

		AND({Per Member Price?}, Users > 0),

		CONCATENATE(Vendor," $", Price, "/user", Frequency )

	)

)


It is much easier to write formulas like this if you use a multi-line syntax.



I can see definitely see why it’s much easier to read; I adjusted my formula to be multi-line. 🙂



There are many ways to accomplish the same end result in a formula.

Use whatever works and makes the most sense to you!

Reply


Terms & Conditions