If Formula returning #Value error

[leepju]

Hi Guys -

I have been trying to make this formula work for a very long time. Following is the formula:

=IF(AND(B2="Yes",X2="3000",AE2="SG&A"),"SG&A"),(IF(AND(AE2="R&D",AK2="Not Applicable"),"R&D -Non LOH"," "),(IF(AND(AE2="R&D",AK2="Yes-Yes"),"R&D - LOH"," ")))

There are multiple conditions to meet in this formula. All of these statements are working if I break these into three. But when I combine them it gives me #value error. Any help would be highly appreciate!!

Thanks in advance guys.

 

[Joe4]

Welcome to the Board!

You are closing your parentheses too early. Try this:

[COLOR=#333333]=IF(AND(B2="Yes",X2="3000",AE2="SG&A"),"SG&A",IF(AND(AE2="R&D",AK2="Not Applicable"),"R&D -Non LOH",IF(AND(AE2="R&D",AK2="Yes-Yes"),"R&D - LOH"," ")))[/COLOR]

 

[leepju]

Just wanted to say I love you Joe4.

 

[Joe4]

Just wanted to say I love you Joe4.

Don't let my wife hear you say that!

 

[leepju]

Ah, all the good one are already taken... Just one more quick question for sake of knowledge. My formula was supposed to be like this:

=IF(AND(B2="Yes",X2="3000",AE2="SG&A"),"SG&A",IF(AND(AE2="R&D",AK2="#N/A"),"R&D -Non LOH",IF(AND(AE2="R&D",AK2="Yes-Yes"),"R&D - LOH"," ")))

I had to change all #n/a to not applicable to make this formula work. Otherwise it would give me #N/A error in a result. Just curious, anyway around that?

Thanks again for your help. Highly appreciated.

 

[Joe4]

There is a difference in checking to see if a cell has a literal text value of "#N/A" (which is what yours would check), and the #N/A error that is returned when it cannot find a value.
If checking for the #N/A error, you would use the ISNA function, i.e.

So instead of

[COLOR=#333333]AK2="#N/A"[/COLOR]

try

[COLOR=#333333]ISNA(AK2)[/COLOR]

See here for details on the ISNA function: https://www.techonthenet.com/excel/formulas/isna.php

 

[leepju]

Makes sense. Thank you so much!