Forumla Limited Characters

[Jared_Ward]

Hello all!

Tried searching, but I couldn’t find anything that answered my question. I believe my formula is correct, and it doesn’t throw an error, however, every time I input it from Notepad ++ it limits it.

Basically I’m trying to assign point values if certain criteria are selected from a multi-select form. I was able to convert it to a stringify data.

So there are nine options.

Option 1, Option 2, and Option 3 are selected. 15 points
Option 1, Option 4, and Option 7 are selected. 10 points
Option 6 and Option 7 are selected. 5 points
Option 9 is selected 0 points.

Etc.

I’m okay with doing multiple, nested IF formulas, but again it limits it? Any reason why?

Thank you!

[Justin_Barrett]

Welcome to the community, Jared! :grinning_face_with_big_eyes: I’m not sure that I understand what you mean when you say that Airtable “limits it” with regard to the nested IF formula. Can you be more specific? What kind of limit are you seeing? Based on your description, nesting IF functions should do the job.

[Jared_Ward]

Hey Justin!

Thanks for the welcome!

I think its a character limit, but here is what I’m entering, via copy and paste from Notepad ++:

IF(
{Option Stringify} = "Option A, Option B", "15", 
	IF({Option Stringify} = "Option B, Option C", "10"),
	IF({Option Stringify} = "Option A, Option D, Option C", "10"),
	IF({Option Stringify} = "Option A, Option E", "10"),
		IF({Option Stringify} = "Option A, Option D, Option E", "5"),
		IF({Option Stringify} = "Option B, Option D, Option E", "5"),
		IF({Option Stringify} = "Option B, Option E, Option F", "5"),
		"0")

The formula is accepted, but when I open up the field again, it gives me this:

IF(
{Option Stringify} = "Option A, Option B", "15", 
	IF({Option Stringify} = "Option B, Option C", "10"))

Let me know if this helps.

Thank you!

[JonathanBowen]

Hi @Jared_Ward - your formula isn’t a nested IF, I’m afraid, just a partially-nested series of IF statements, so that’s why it isn’t working. So you could fix the nesting, but a better option in this scenario is to use SWITCH() :

SWITCH(
  {Option Stringify}, 
  "Option A, Option B", "15",
  "Option B, Option C", "10",
  ...
) 

This evaluates the field Option Stringify and matches it to one of your patterns below. For the pattern it matches it returns the second argument, i.e. 15, 10 etc.

JB

[JonathanBowen]

A question though - does each option have a fixed score, so, perhaps:

Option A = 10
Option B = 5
Option C = 6

and so on? If you’ve got 4 or 5 options, the possible set of combinations will get large pretty quickly so even with a SWITCH formula, this might get a bit unmanageable. Seems like there should be a way to identify the options in the formula and add the scores of these.

JB