3 Replies Latest reply on Mar 15, 2016 9:47 AM by Patrick Cotter

# Complex IF statements in a calculated field

Hi all,

I was hoping I could get some help with building a calculated field. What I'd like to do is create a new measure that checks multiple cells in the same row and returns a value based on the combination of their values. These cells are binary, with a 1 meaning a "yes" and a 0 meaning a "no."

For example, let's say we have a dataset of employees in an office building, the building has 8 floors, the dataset looks something like this:

NameFloor 1
Floor 2
Floor 3
Floor 4
Floor 5
Floor 6
Floor 7
Floor 8
Peter11000000
Sarah00001000
Jean00000011

Peter and Jean have offices on two floors each. I'd like to be able to build a field that returns a value of "Floor X," where X is the floor the employee works on, with a return of "Multiple Floors" if they work on more than one (though it would be nice to know how to also return a value of "Floor X and X"). The eventual goal is to be able to filter on this field.

Thanks so much!

• ###### 1. Re: Complex IF statements in a calculated field

A QnD way to do that is to add [Floor1]+[floor2]+ ...

IF ([Floor1]+[floor2]+ ...)>1 then  "Multiple" else

if [Floor1] = 1 then "Floor1" elseif

[Floor2] = 1 then "Floor2" elseif

...

end

end

1 of 1 people found this helpful
• ###### 2. Re: Complex IF statements in a calculated field

Getting "Floor1 and Floor2" would be tedious, but you could build a giant nested IF for the various combinations.

And maybe you could work up something like this:

First have a preliminary calc that grabs the first floor, whether or not there is a "Multiple" in the calc I did in my first reply.

Then do this:

if [initial pass calc] = "Multiple" then

(do something here to skip pas the first floor value  in [preliminary calc] and grab the second one)

My first thoughts are that this will still be a tedious calc to type out.  You can tell that I don't want to.  It depends on how badly you want that.

1 of 1 people found this helpful
• ###### 3. Re: Complex IF statements in a calculated field

Thanks! That first reply was exactly what I needed. I'm planning on just going with that, but I want to know how to break it down with the more complex answer if I end up needing to at some point.

Thanks again!