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.
<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
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