"IF" function returns error when using arrays

[Jim Schueren]

IF(D14=2,"yes","no") I want the cell to return a YES if
all the cells are equal to 2, a NO if some of then aren't
a 2.

According to the wizard, this works. When inputing the
fields via the wizard excel says this is true (assuming
there are 2's in there) and that the value of the cell is
YES. However, when "OKing" the wizard, the cell actually
returns a "#Value!" error. So, it appears to work via the
wizard but won't work in real life. Any ideas?

 

[immanuel]

To check for a single occurrence of 2 in D14:

=IF(ISNA(MATCH(2,D14,0)),"No","Yes")

To check if ALL cells with values in D14 = 2:

=IF(COUNTIF(D14,2)=COUNT(D14),"Yes","No")

To include blank cells in the equation (D14 may include blank cells):

=IF(COUNTIF(D14,2)=(COUNT(D14)+COUNTBLANK(D14)),"Yes","No")

/i.

 

[mpb1]

Hi Jim,
I may sound trite but remember to ctl-shift-enter
Cheers,
Mathew

 

[Aladin Akyurek]

That should be:

=IF(AND(D14=2),"yes","no")

or just

=--AND(D14=2)

with the formula cell custom formatted as

[=1]"Yes";[=0]"No"

Both formulas must be confirmed with control+shift+enter, not just with
enter.

 

[Alan Beban]

The first formula does not seem to require array entering. xl2000

Alan Beban

That should be:

=IF(AND(D14=2),"yes","no")

or just

=--AND(D14=2)

with the formula cell custom formatted as

[=1]"Yes";[=0]"No"

Both formulas must be confirmed with control+shift+enter, not just with
enter.

=IF((D14)=2,"yes","no")
Array entered, ie (ctrl + shift + enter)

Dan E

 

[Dan E]

My mistake, sorry!

Dan E

That should be:

=IF(AND(D14=2),"yes","no")

or just

=--AND(D14=2)

with the formula cell custom formatted as

[=1]"Yes";[=0]"No"

Both formulas must be confirmed with control+shift+enter, not just with
enter.

=IF((D14)=2,"yes","no")
Array entered, ie (ctrl + shift + enter)

Dan E

 

[Tim Kredlo]

Myrna,

That's what I thought... but

If I set D1=2, D2=2, D3=2, & D4=5 (NOT 2) and I
shift/ctrl/enter "=IF((D14)=2,"yes","no")" formula in
E1, E1 beomes 'yes'. Since the person with the original
problem was looking to get an answer into a single cell,
I don't see how the array formula would work.

The formula may return 4 values, but I am only seeing a
single value in a single cell.

Can you share how you are doing that?

-----Original Message-----

Not true if it's entered as an array formula, with

CTRL+SHIFT+ENTER. Each cell in the range is

 

[Myrna Larson]

This array formula

=IF((D14)=2,"yes","no")

does return 4 values, but to see all 4, you have to select 4 cells, say E1:E4, and enter the
formula with CTRL+SHIFT+ENTER.

That formula of course does not tell you whether *all* values in the range are equal to 2. It
gives you one result for each cell in the range.

To answer the question re do all cells contain 2, you need to get Excel to construct the list of
4 TRUE/FALSE values, convert them to numbers, and give you the SUM. Then you check whether that
sum is 4. Here's one way to do that:

=IF(SUM(IF(D14=2,1,0))=4,"yes","no")

and here's another:

=IF(SUM(1*(D14=2))=4,"yes","no")

Those are both array formulas.

But for this task, you could simply use COUNTIF

=IF(COUNTIF(D14,2)=4,"yes","no")

or

=IF(SUMPRODUCT(1*(D14=2))=4,"yes","no")

The last 2 are not array formulas.

Myrna,

That's what I thought... but

If I set D1=2, D2=2, D3=2, & D4=5 (NOT 2) and I
shift/ctrl/enter "=IF((D14)=2,"yes","no")" formula in
E1, E1 beomes 'yes'. Since the person with the original
problem was looking to get an answer into a single cell,
I don't see how the array formula would work.

The formula may return 4 values, but I am only seeing a
single value in a single cell.

Can you share how you are doing that?