Nested IF formula

[datadummy]

I am attempting to write a nested if formula that currently looks like this =IF(I15:L15="NA","NA",IF(I15:L15="No","N",IF(I15:L15="Y","Y",""))) and am not getting the expected results. Currently cells I-L all equal NA. I'm sure I did something wrong and am looking for guidance to fix this or even use a better more simplistic approach.

 

[Joe4]

That is not a valid function.
You cannot check to see if a whole range of cells equals a single value like that.

Are you trying to check if ALL cells in that range equal that value, or only one of the cells has to equal that value?
Use COUNTIF instead, i.e.

=IF([COLOR=#ff0000]COUNTIF(I15:L15,"NA")=4[/COLOR],"NA",...

Make that type of change in the other spots of your formula too.

 

[Dave8899]

I may have missunderstood what you are tying to do but, are cells I-l merged? If they are just use I15 not I15:L15. that should solve it.

If if they are 3 separate cells I would need a bit more info.

 

[datadummy]

Am I able to check that range for the 3 potential values Y,N,NA and return the corresponding values?

 

[Dave8899]

Hi not quite sure what you want, are they 3 separate cells? Do all 3 cells give the same value every time?

If so just select 1 cell just simply use an = I 15 for example, if they all give separate answers, may have yo look at something more complicated��

 

[Joe4]

Am I able to check that range for the 3 potential values Y,N,NA and return the corresponding values?

COUNTIF allows you to count how many times a certain value appears in a range of cells.
So you can use it check to see if a particular value exists in ALL cells, one cell, no cells, etc.

To check for multiple values, you would just use multiple references to the formula, checking for each value separately.

If you need more help, please give more details, especially answering the questions we asked about what exactly you are trying to do (such as the question I asked about checking for a certain value in ALL the cells of your range, or just one; as well as the questions others asked).

 

[datadummy]

So each cell is separate, but with that said its likely that each cell will have the same value in it most of the time. Currently the way I have it setup is formulas in J-L that state if column I is N then each of the cells J-L populates NA so in the event column I has N and the rest have NA then M should be NA. Next if column I has Y in it then my formula in column M needs to address each column I-L and if it has all Y the M should be Y but if there's a single N then M should be N. Hope this helps.

 

[Joe4]

Your conditions seem a bit confusing, but maybe something like this:

[COLOR=#333333] =IF(COUNTIF(I15:L15,"NA")=4,"NA",IF([/COLOR][COLOR=#333333]COUNTIF(I15:L15,"N")>0[/COLOR][COLOR=#333333],"N",IF([/COLOR][COLOR=#333333]COUNTIF(I15:L15,"Y")>0[/COLOR][COLOR=#333333],"Y","")))[/COLOR]

 

[datadummy]

Nailed it!! That did the trick, thank you!!

 

[Joe4]

You are welcome.