Array behavior in the IF function

[zack_]

Hi Guys,

I am new to incorporating arrays into my formulas and am having issues at times -- I guess one of the more confusing aspects of the array formulas is when I put them in IF functions, and sometimes it seems like the IF function will only pluck out the those statements that are TRUE and use them while other times the statement will use both true AND false criteria but seems to eventually eliminate the false criteria...

For example: I have the following criteria, and I am trying to calculate a unique count for all values that occur on 1/1/2001. The answer is 2.

1/1/2001	4
1/1/2001	4
1/1/2001	4
1/1/2001	4
11/12/2004	1
1/1/2001	5

<tbody>
</tbody>

when I use the formula: =SUM(--(FREQUENCY(IF(C18:C23=C18,D18:D23),D18:D23)>0)) I get the right answer. The IF function appropriately places only the true values in the data array of the first argument of the frequency function.

When I use the formula =SUM(--(IFERROR(IF(C18:C23=C18,FREQUENCY(D18:D23,D18:D23)>0),0))) I also arrive at the correct answer of two. But it seems counter intuitive to me that this formula could work. How does excel, decide to put all values (both true and false from the logical test argument) into the frequency function (true statement) rather than just the true values. Eventually those values are eliminated... but in experimenting I have found that an array in the logical test that has both true and false values, and there are both true and false statements then the IF function by default seems to carry out only the true functions. For example this scenario: =IF(C18:C23=C18,SUM(D18:D23),SUM(D18:D23)) -- both functions are calculated when i use formula evaluator but the false function is basically discarded and the true statement is completely executed.

I am just trying to understand the array formulas so that I can predict its result... is it fair to say that in the presence of any true values in an IF statement the formula will by default always calculate the true statement of the if formula?

Thanks in advance for any explanation.
-Zack

 

[barry houdini]

When I use the formula =SUM(--(IFERROR(IF(C18:C23=C18,FREQUENCY(D18:D23,D18:D23)>0),0))) I also arrive at the correct answer of two.

Hello Zack, welcome to MrExcel

I don't think you can guarantee that you'll get the correct result with that second formula, what result do you get if you change the 1 in D22 to a 5?

When you use that last formula you suggested, i.e.

=IF(C18:C23=C18,SUM(D18:D23),SUM(D18:D23))

then the formula will return an "array" of 6 values, one each for each cell in C18:C23, so in your case the TRUE and FALSE arguments are the same and you can't easily distinguish from the result what it is doing.

If you change it to this:

=IF(C18:C23=C18,1,0)

Then the result would be something like

{1;1;0;0;1;1}

Of course you can't normally see that result if you place the formula in a single cell, you will normally see just the first value from that array - to see the whole result select the cell with the formula then press F2 and then F9. Normally you would use another function around the formula to process that array, e.g. SUM, AVERAGE or similar.

 

[zack_]

Hello Zack, welcome to MrExcel

I don't think you can guarantee that you'll get the correct result with that second formula, what result do you get if you change the 1 in D22 to a 5?

When you use that last formula you suggested, i.e.

=IF(C18:C23=C18,SUM(D18:D23),SUM(D18:D23))

then the formula will return an "array" of 6 values, one each for each cell in C18:C23, so in your case the TRUE and FALSE arguments are the same and you can't easily distinguish from the result what it is doing.

If you change it to this:

=IF(C18:C23=C18,1,0)

Then the result would be something like

{1;1;0;0;1;1}

Of course you can't normally see that result if you place the formula in a single cell, you will normally see just the first value from that array - to see the whole result select the cell with the formula then press F2 and then F9. Normally you would use another function around the formula to process that array, e.g. SUM, AVERAGE or similar.

Hi Barry,

Thank you for the welcome and for your insight. You are right about the second formula, when I change the 1 to a 5 I do in fact get an incorrect answer. I sort of understand why it is wrong, but I am going to keep playing around and experimenting with the formula to truly get it down.