Nested Excel Function

chegra26

Board Regular
Joined
Jun 7, 2014
Messages
87
I have the 6 columns that I need to include in an IF formula that I am having issues with.
Column A is Ethnicity,Values are "Y" for Yes and "N" for No
Column B is America Indian, Values are "0" mean not American Indian and "1" meaning person is America Indian
Column C is Asia, Values are "0" means not Asian and "2" meaning Asian
Column D is Africa American, Vales are "0" meaning not Africa American and "3" meaning person is African America
Column E is Native Hawaiian, Values are "0" meaning not Native Hawaiian and "4" meaning person is Native Hawaiian
Column F is White, Values are "0"meaning not White and "5" meaning person is white
Column G is my formula in which I need my results to be either: "Hispanic" based on the Y value in Column A or American Indian if Column B ="1" or Asian if Column C ="2" or African American if Column D ="3" or Native Hawaiian if Column E="4" or White if Column F ="5" or Two or More if there is a combination of columns B-G that <> "0".
Column A Trumps any of the results.
I have the following formula which works except for the result of Two or More should the person have more than one race.

=IF(A2="Y","Hispanic",IF(B2="1","American Indian",IF(C2="2","Asian",IF(D2="3","African American",IF(E2="4","Native Hawaiian",IF(F2="5","White","Two r More")))))

Please help
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Based on your description, this should do what you need:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Ethnicity</td><td style=";">American Indian</td><td style=";">Asian</td><td style=";">African Camerican</td><td style=";">Native Hawaiian</td><td style=";">White</td><td style=";">Result</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">N</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style=";">Two or More</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">N</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";">African Camerican</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">N</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Y</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">5</td><td style=";">Hispanic</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">N</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">5</td><td style=";">White</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet236</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">A2="Y","Hispanic",IF(<font color="Green">COUNTIF(<font color="Purple">B2:F2,">0"</font>)>1,"Two or More",CHOOSE(<font color="Purple">SUM(<font color="Teal">B2:F2</font>),B$1,C$1,D$1,E$1,F$1</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

Formula copied down.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,794
Messages
5,655,339
Members
418,190
Latest member
Timex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top