Help

Re: Formula to use across multiple columns?

Solved
Jump to Solution
1797 0
cancel
Showing results for 
Search instead for 
Did you mean: 
lexx
4 - Data Explorer
4 - Data Explorer

I have 3 separate fields and I want the 4th field to show a result based on the 3.

The responses can be yes, no, maybe

If at least 2 of the 3 columns are YES, then result should be YES
If at least 2 of the 3 columns are NO, then result should be NO
If at least 2 of the 3 columns are MAYBE, then the result should be MAYBE
If each column has one response of each, then the result should be MAYBE

I am new to Airtable and am more familiar with Excel, so a simple formula or guidance would be helpful. My current Excel has a long formula of nested IF and COUNTIF. Are there equivalents of those on here?

1 Solution

Accepted Solutions

Doing this I realized that you just need to add a bunch of ‘Maybes’ in the third switches as the default option. Even you can simplify green sub-branches as ‘IFs’ because there are 2 ‘Maybes’.

IMG_20190429_153531.jpg

Also, I noticed that I missed several commas because of the copy-pasting (now the @JonathanBowen’s solutions begins to feel better :smile_cat: ). Anyway, the important is to understand the method and the logic, and you could do it in other scenarios.

I leave the full formula, I think now it’s correct:

SWITCH(
	'Col1',
	'Y', SWITCH(
		'Col2',
		'N', SWITCH(
			'Col3',
			'Y', 'Yes',
			'N', 'No',
			'Maybe'
		),
		'M', IF(
			'Col3' = 'Y',
			'Yes',
			'Maybe'
		),
		'Yes'
	),
	'N', SWITCH(
		'Col2',
		'Y', SWITCH(
			'Col3',
			'Y', 'Yes',
			'N', 'No',
			'Maybe'
		),
		'M', IF(
			'Col3' = 'N',
			'NO',
			'Maybe'
		),
		'No'
	),
	'M', SWITCH(
		'Col2',
		'Y', IF(
			'Col3' = 'Y',
			'Yes',
			'Maybe'
		),
		'N', IF(
			'Col3' = 'N',
			'No',
			'Maybe'
		),
		'Maybe'
	)
)

Also, what @Gary_Ford said is very true.

See Solution in Thread

14 Replies 14
Gary_Ford
5 - Automation Enthusiast
5 - Automation Enthusiast

Give this a look.

5f73751092c6afb3485d0dfe997b3809227f5002.png

Nested IF formulas

Basics of the IF function The IF function will return one of two values depending on whether a logical argument is true or not. Written in the format: IF([logical argument], [value1], [value2]) thi...

Thank you for your response, but I have already looked at that and it doesn’t achieve what I need to do. It doesn’t seem like Airtable does cell ranges like Excel.

This is my current Excel Formula for reference of what I need to translate into Airtable formulas.

=IF(COUNTIF(C2:E2,“Yes”)=3,“YES”,IF(COUNTIF(C2:E2,“Yes”)=2,“YES”,IF(COUNTIF(C2:E2,“Maybe”)=3,“MAYBE”,IF(COUNTIF(C2:E2,“Maybe”)=2,“MAYBE”,IF(COUNTIF(C2:E2,“No”)=3,“NO”,IF(COUNTIF(C2:E2,“No”)=2,“NO”,“MAYBE”))))))

Hi @lexx - Airtable doesn’t have a COUNTIF function, so you can’t replicate this directly (would be great if it did!). I tried various ways to solve this problem using FIND(), MID() etc, but all horrible or didn’t work. The best I could come up with is a total hack and a bit flukey:

Ignoring the order of the answers I think you’ve got 10 possible combinations of answers:

Screenshot 2019-04-29 at 10.42.15.png

3 Yes
3 No,
3 Maybe? (ignore the “?” for now, will explain)
2 Yes, 1 No
etc, etc

If you join these 3 columns together and then find the length of each concatenated string you get:

Screenshot 2019-04-29 at 10.44.30.png

By chance, the lengths are different for all combinations:

Screenshot 2019-04-29 at 10.46.12.png

So you could now do a SWITCH() function on the length to determine the answer you require:

SWITCH(
Length,
6, "No",
7, "No",
8, "Yes",
.
.
.
18, "Maybe?"
)

And this is why you need the “?” at the end of the “Maybe”. If you don’t have this, you don’t get a unique value for each combo, so the hack doesn’t work.

Obviously, this isn’t a general solution to the problem, but a very specific one (and even then, only if you can tolerate “Maybe?” instead of “Maybe”. Or “Maybe!” or “May-be”. All of them terrible, TBH :slightly_smiling_face: ).

JB

SWITCH(
	'Col1',
	'Y', SWITCH(
		'Col2',
		'N', SWITCH(
			'Col3',
			'Y', 'Yes',
			'N', 'No'
		),
		'M', SWITCH(
			'Col3',
			'Y', 'Yes'
			'M', 'Maybe'
		)
		'Yes'
	),
	'N', SWITCH(
		'Col2',
		'Y', SWITCH(
			'Col3',
			'Y', 'Yes',
			'N', 'No'
		),
		'M', SWITCH(
			'Col3',
			'N', 'NO'
			'M', 'Maybe'
		)
		'No'
	),
	'M', SWITCH(
		'Col2',
		'Y', SWITCH(
			'Col3',
			'Y', 'Yes',
			'M', 'Maybe'
		),
		'N', SWITCH(
			'Col3',
			'N', 'No'
			'M', 'Maybe'
		)
		'Maybe'
	)
)

I’ve used capital letters for values and full words for results. You can change the values and it will work always. This is the schema I’ve followed:

IMG_20190429_153531.jpg

And more generally applicable too.

You were ingenious! :grinning_face_with_big_eyes: But I think the “logic approach” is more solid.

Gary_Ford
5 - Automation Enthusiast
5 - Automation Enthusiast

Keep in mind that AirTable is not a spreadsheet it is a relational database.
Your spreadsheet mind-set is fighting you with bad information working with relational databases. Spreadsheets and relational databases are not the same animal.

In AirTable each row is a record. For example in a Contact Database each row would be a different contact and the columns would hold the contact information. Records are composed of fields(columns). Fields or ( columns) are where the data lives. Each column is set up to do only one thing. It can hold data such as a contact first name. A column can also have a formula and the formula will act on every row for that column. What happens in columns (format, formulas, whatever) is consistent for every record. You can not have a custom calculation in column(field) 3 of row(record) 6 and a different calculation in column 3 row 7. Whatever you set up a column to do is what the columns does for every record (row) in the database. This does not mean that you can not achieve the same results you can with a spread sheet, but the way you are going to organize the data is completely different. You will never get there thinking in spreadsheet mind-set. This is a completely different tool. Read the airtable section that describes relational databases. Then look at the examples. You have to understand how relational databases work so that you can understand how you need set up your data in various related databases in order to setup a structure of related data that will then let you solve your particular problem. Relational Databases are not for every problem.

Gary

Thanks! This is great, but it doesn’t seem to be accounting for the 6 different Yes, No, Maybe combinations. Unless I’m doing something wrong…still taking a try at figuring all this out…

I totally missed your fourth rule :man_facepalming: , but completing the schema and adding it to the formula should be easy.

Doing this I realized that you just need to add a bunch of ‘Maybes’ in the third switches as the default option. Even you can simplify green sub-branches as ‘IFs’ because there are 2 ‘Maybes’.

IMG_20190429_153531.jpg

Also, I noticed that I missed several commas because of the copy-pasting (now the @JonathanBowen’s solutions begins to feel better :smile_cat: ). Anyway, the important is to understand the method and the logic, and you could do it in other scenarios.

I leave the full formula, I think now it’s correct:

SWITCH(
	'Col1',
	'Y', SWITCH(
		'Col2',
		'N', SWITCH(
			'Col3',
			'Y', 'Yes',
			'N', 'No',
			'Maybe'
		),
		'M', IF(
			'Col3' = 'Y',
			'Yes',
			'Maybe'
		),
		'Yes'
	),
	'N', SWITCH(
		'Col2',
		'Y', SWITCH(
			'Col3',
			'Y', 'Yes',
			'N', 'No',
			'Maybe'
		),
		'M', IF(
			'Col3' = 'N',
			'NO',
			'Maybe'
		),
		'No'
	),
	'M', SWITCH(
		'Col2',
		'Y', IF(
			'Col3' = 'Y',
			'Yes',
			'Maybe'
		),
		'N', IF(
			'Col3' = 'N',
			'No',
			'Maybe'
		),
		'Maybe'
	)
)

Also, what @Gary_Ford said is very true.

Perfect. Thank you so much! and here I was just only beginning to understand the switch formula haha, I need to make some time to sit down and just look at it to fully understand it. But this was a big help!

@JonathanBowen - thank you for your response as well, it made sense in its own way but more extra parts than needed.

@Gary_Ford thank you for the insight, Yes I am still getting myself learned on the differences as I have never used a database application before. I have only used Excel. I will probably be using airtable more as I do like some of the capabilities it provides, so I will definitely continue to read up on it.

Thank you again everyone!

It is like an IF, but with several checks, similar to a nested IFs chain. Let’s say you have an Status fields for some tasks, and you want to output an emoji depending on it, so the switch is:

9924aac4233469aa9d6ff05c63a91ea273b72ba4.png

And this is what the help says:

Captura de pantalla 2019-05-02_10-52-11_a. m..png

Well, the default result should be an emoji to be consistent, so:

SWITCH(
	{Status},
	'Waiting', '⏳',
	'Working', '🔄',
	'Done', '✅',
	'❓'
)