What is Ctrl + Shift + Enter ?

[Guest]

i was reading another post and came accross the tool Ctrl Shift Enter. What
is this?

the following formula does not work if you enter it it in a cell and simply
hit enter, but if you press Ctrl Shift Enter, Excel puts { } around the
formula, and it works. the formula does NOT work if you type the {}'s
manually.

=AVERAGE(IF(array1>0,array1)) (array1 is the name of an array of numbers)

how does one know when to use Ctrl Shift Enter?

 

[Chip Pearson]

Using CTRL+SHIFT+ENTER enters the formula as what is called an "array
formula". Array formulas work with arrays of data (a series of values)
rather than single values. An array formula can be used to return a set of
values to more than one cell or to aggregate (e.g., via SUM or AVERAGE) a
series of value into a single value.

In the example you provide,

=AVERAGE(IF(array1>0,array1))

entering the formula as an array formula causes each value in 'array1' to be
compared to greater than zero, and if that comparison is TRUE (x>0), that
value is passed into AVERAGE. If the comparison is FALSE, the Boolean value
FALSE is passed to AVERAGE, which ignores TRUE and FALSE values. For
example, if array1 was A1:A3 with values 1,0,3, each of these would be
compared to 0, and if greater than zero {TRUE, FALSE, TRUE}, that value is
passed to AVERAGE. In this case, AVERAGE would get the array of values {1,
FALSE, 3}. Since AVERAGE ignores the FALSE value, it averages only values 1
and 3, for a result of 2.

See www.cpearson.com/excel/array.htm for an explanation of the two types of
array formulas and examples of both.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Dave Peterson]

Start your journey at Chip Pearson's site:
http://cpearson.com/excel/array.htm

And then visit Bob Phillips' site:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[T. Valko]

See this:

http://cpearson.com/excel/array.htm

Biff

 

[Guest]

Thank you very much. but why does the formula, =AVERAGE(IF(array1>0,array1)),
not simply work without the ctrl shift enter? why can i not just press enter
and have it recognize that i want the average of the values iff >0?

 

[Guest]

never mind, obviously i replied before going to the very useful website...

thanks again

 

[Chip Pearson]

not simply work without the ctrl shift enter?

In this particular case, the "problem" is with the way IF handles multiple
cells in the test argument. Unless the IF function is array-entered, IF will
return #VALUE. E.g,. =IF(A1:A3,TRUE,FALSE) will return an error. The use of
CTRL+SHIFT+ENTER tells IF to expect and process an array of values. Other
functions work quite peacefully with arrays even when they are not array
entered. For example, the ROW function will return the row number of the
first cell in the reference. ROW(A1:A3) returns 1. The function
=SUM(ROW(A1:A3)) will return 1 if not array entered or 6 if array entered.
This is because the array entry tells ROW to return an array of number
{1,2,3} rather than the single valued result 1.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)