Help

Need Help with nested IF statements combining 6 fields into one

Topic Labels: Formulas
Solved
Jump to Solution
715 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Markus_Wernig
8 - Airtable Astronomer
8 - Airtable Astronomer

I can’t seem to figure this one out. I have 3 Writer fields with 3 corresponding Share fields.

Screen Shot 2021-03-13 at 2.55.29 PM

So far I am only able to combine the WRITER fields with the following formula:

{WRITER 1}
& IF(
AND(
{WRITER 1}, OR({WRITER 2}, {WRITER 3}
)
), ', ’
)
& {WRITER 2}
& IF(
AND(
{WRITER 2}, {WRITER 3}),
', ')
& {WRITER 3}

But I’d like to combine WRITER and corresponding SHARE fields and then combine everything in the ALL NAMES COMBINED formula field.

The final combinations should read:

John (BMI: 20457, 50%), Michael (BMI: 20691, 50%)
John (BMI: 20457, 50%), Michael (BMI: 20691, 25%), Charles (PRS: 30918, 25%)
Charles (PRS: 30918, 100%)

Yes, the extra “)”, commas, and percentage signs have to be added in the formula, but my question is, can all this be done in one swoop in the ALL NAMES COMBINED formula field, or do I have to create 3 “PRE-COMBINED” fields for the 3 Writers and their percentages and then combine these fields with my original formula?

It would be great if it could be done in one formula, as I already have way too many fields in my table.

Thank you in advance for pointing me in the right direction. I really do appreciate it!

1 Solution

Accepted Solutions

Hi @Markus_Wernig!
If you create a new record with no values in any of the 6 different fields your formula reference, you will see that your formula outputs “, %), %)”.

The source or the first comma and first "%) are located on line #6 in your code.
The source or the second comma and second "%) are located on line #9 in your code.

Airtable reads line 6 as

Insert value from {WRITER 2} followed by a comma, followed by {WRITER 2 SHARE}, followed by "%)

If there are no value entered in the fields {WRITER 2} and {WRITER 2 SHARE} the output is

, %)

Same output for line 9.

To fix the problem you need to tell Airtable to only write out these outputs when certain conditions are met. This should hopefully work for you and if not, at least give you an idea how to achieve what you are after.

IF({Writer 1},
	{Writer 1}&''&
	IF({Writer 1 Share},
		', '&{Writer 1 Share}&'%)',
		')'
	)
)&''&
IF(
	OR(
		{Writer 2},{Writer 3}
	),
	", "
)&''&
IF({Writer 2},
	{Writer 2}&''&
	IF({Writer 2 Share},
		', '&{Writer 2 Share}&'%)',
		')'
	)
)&''&
IF({Writer 3},
	", "
)&''&
IF({Writer 3},
	{Writer 3}&''&
	IF({Writer 3 Share},
		', '&{Writer 3 Share}&'%)',
		')'
	)
)

See Solution in Thread

3 Replies 3
Markus_Wernig
8 - Airtable Astronomer
8 - Airtable Astronomer

I have made some progress with my attempt at combining these 6 fields into one formula field, but I can’t get rid of one or two extra % signs that always pop up.

What am I not seeing? Thank you!

Screen Shot 2021-03-13 at 7.12.09 PM

Here’s how far I got with my formula:

IF(AND(
	{WRITER 1},{WRITER 1 SHARE}),
	{WRITER 1} &', '& {WRITER 1 SHARE} &'%)')
&IF(AND(
	{WRITER 1 SHARE},OR({WRITER 2 SHARE},{WRITER 3 SHARE})),", ")
	& {WRITER 2} &', '& {WRITER 2 SHARE} & '%)'
&IF(AND(
	{WRITER 2 SHARE},{WRITER 3 SHARE}),", "
	&{WRITER 3} &', '& {WRITER 3 SHARE} & '%)',
	{WRITER 3} &', '& {WRITER 3 SHARE} & '%)'
)

Hi @Markus_Wernig!
If you create a new record with no values in any of the 6 different fields your formula reference, you will see that your formula outputs “, %), %)”.

The source or the first comma and first "%) are located on line #6 in your code.
The source or the second comma and second "%) are located on line #9 in your code.

Airtable reads line 6 as

Insert value from {WRITER 2} followed by a comma, followed by {WRITER 2 SHARE}, followed by "%)

If there are no value entered in the fields {WRITER 2} and {WRITER 2 SHARE} the output is

, %)

Same output for line 9.

To fix the problem you need to tell Airtable to only write out these outputs when certain conditions are met. This should hopefully work for you and if not, at least give you an idea how to achieve what you are after.

IF({Writer 1},
	{Writer 1}&''&
	IF({Writer 1 Share},
		', '&{Writer 1 Share}&'%)',
		')'
	)
)&''&
IF(
	OR(
		{Writer 2},{Writer 3}
	),
	", "
)&''&
IF({Writer 2},
	{Writer 2}&''&
	IF({Writer 2 Share},
		', '&{Writer 2 Share}&'%)',
		')'
	)
)&''&
IF({Writer 3},
	", "
)&''&
IF({Writer 3},
	{Writer 3}&''&
	IF({Writer 3 Share},
		', '&{Writer 3 Share}&'%)',
		')'
	)
)

Thank you so much, @momentsgoneby80 !

Your solution works flawlessly. I. had tried to do what you were suggesting, but I clearly didn’t know how to get there. Following your code made things very clear to me. I am learning these things one step at a time…

Thank you again!