2 Replies Latest reply on Apr 10, 2012 1:14 PM by Cole Pacak

# Replicating an IF and COUNTIF Excel function to determine ethnicity in Tableau

In a table, I have five columns: ID, Hispanic, Asian, Black, and White. The ID column contains integers, and each one of the ethnicity columns has a "Y" or "N". It looks like this:

A: IDB: HispanicC: AsianD: BlackE: White

1

YYYY
2YNNY
3NYNY
4NYNN
5YNNY
6NNYY
7YNYY
8YNNY
9NYNY
10NNYN

Don't ask why, but the state where I live uses the following criteria to determine ethnicity:

-If "Y" in [Hispanic], then "Hispanic"

-If there is more than one "Y" in [Asian], [Black], and/or [White], then "Two or More Races"

After all that:

-If not "Hispanic" or "Two or More Races" and "Y" in [Asian], then "Asian"

Ditto for [Black] and [White]

As a result, in Excel I used the following formula:

=IF(Hispanic="Y","Hispanic",IF(COUNTIF(C2:E2,"Y")>1,"Two or More Races",IF(Asian="Y","Asian",IF(Black="Y","Black",IF(White="Y","White")))))

Can I replicate this function as a calculated field in Tableau?

Thanks

• ###### 1. Re: Replicating an IF and COUNTIF Excel function to determine ethnicity in Tableau

If [Hispanic]="Y" then "Hispanic" elseif

(if [Asian]="Y" then 1 else 0 end + if [Black]="Y" then 1 else 0 end + if [White]="Y" then 1 else 0 end)>=2

then "Two or More Races"

elseif  [Asian]="Y" then "Asian"

elseif ... and so on  end

Should do it

• ###### 2. Re: Replicating an IF and COUNTIF Excel function to determine ethnicity in Tableau

Brilliant! Thank you so much.