Simple array

[normpam]

I'm trying to learn about basic array formulas. Following is my example:

Booked Actual
33 32
9 16
8 6
21 30
27 25
16 16
13 43
19 49

Formula: {=sum(c4.c11>=b4.b11)} - entered without the brackets, using CTRL-****-ENTER

The result should be 5 for the number of times that 'actual' is greater than or equal to 'booked'. But I'm getting zero. When I use F9 the formula shows =sum(FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)

What am I missing here?

Thanks!

 

[sheetspread]

You need an If, sumproduct, and/or +0, -- to convert the True/False to 1/0.

Excel 2010
	A	B
1	Booked	Actual
2	33	32
3	9	16
4	8	6
5	21	30
6	27	25
7	16	16
8	13	43
9	19	49
10
11		5
12		5
13		5
Sheet13

Cell Formulas
Range	Formula
B12	=SUMPRODUCT(--(B2:B9>=A2:A9))
B11	{=SUM(--(B2:B9>=A2:A9))}
B13	{=SUM(IF(B2:B9>=A2:A9,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Note that sumproduct doesn't require ctrl-shift-enter

 

[Scott T]

This is a logical test and will return true or false.

=C1:C11>=B1:B11

Excel 2010
	B	C
1	33	32
2	9	16
3	8	6
4	21	30
5	27	25
6	16	16
7	13	43
8	19	49
Sheet5

This should give you want you want with out CSE

=SUMPRODUCT(--(C1:C8>=B1:B8))

You could use -- to turn the true/false into 1/0 and then sum but you would need CSE

=SUM(--(C1:C8>=B1:B8))

 

[normpam]

Thanks... what I was missing was why didn't Excel evaluate the 'True' and 'False' results from the formula as 0's and 1's?

 

[sheetspread]

They're sometimes interchangeable (e.g. vlookup accepts true/false or 1/0, so does the address property in VBA). Or you might prefer True/False to distinguish items, like in Goto/specialcells method that can highlight boolean.

 

[Scott T]

Thanks... what I was missing was why didn't Excel evaluate the 'True' and 'False' results from the formula as 0's and 1's?

If you preform a math operation on the true and false results it will become ones and zeros. you can +0, *1 or -- (change the sign then change it back).