Automatic Gala floor assignments using conditional logic

Hi there!

I am organizing seating arrangements for a gala. Each table number I assign to a group corresponds with the floor their table will be located on e.g. (Table 1-19 = Floor One, table 2-29 = Floor 2, etc.)

Right now, I am entering the table numbers for each group in a Number field and want to create a Formula field that automatically outputs what the corresponding floor is. (e.g. If their table number is “43” then the formula outputs “Floor 4”). I am doing this so I can group the view by the Floor # formula output.

Is a combination of Nested IF & AND statements the way to go? Here is the code I tried (it doesn’t work because it only returns “true/false” or “Floor One”). Open to any suggestions and thanks for the help!

 IF(
      {Table Number} > 0, AND({Table Number} < 20),
       "Floor One", 
       IF(
            {Table Number} => 20, AND({Table Number} < 30),
            "Floor 2"
             )
           )

For anyones reference, here is the solution:

IF(AND({Table Number} > 0, {Table Number} <20), “Floor 1”,
IF(AND({Table Number} >= 20, {Table Number} <30), “Floor 2”,
IF(AND({Table Number} >= 20, {Table Number} <40), “Floor 3”,
IF(AND({Table Number} >= 40, {Table Number} <50), “Floor 4”,
IF({Table Number}= “”, “Unassigned to table”
)))))

It’s a lot simpler using just the first character: IF(Table, 'Floor '& LEFT(Table,1), 'Unassigned to table').

@Elias_Gomez_Sainz - that won’t work for tables 2-9 (on floor 1) :wink:

Although, for consistency, it would make sense to have Floor 1 have tables 10-19, Floor 2 have tables 20-29 and so on, in which case it works. It isn’t clear from the original post how many tables there are or might be on Floor 1.

JB

Of yes, I oversimplified in my head :smile_cat:. In fact, all is a +1. And recreating the Formula I realized that rounding is our friend :muscle:

IF(
	Table,
	'Floor ' &
	ROUNDUP(
		Table/10,
		0
	),
	'Unassigned to table'
)

1 Like