Posted to microsoft.public.excel.misc
|
|
What is Ctrl + Shift + Enter ?
never mind, obviously i replied before going to the very useful website...
thanks again
"Chip Pearson" wrote:
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(array10,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 (x0), 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)
"lawson" wrote in message
...
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(array10,array1)) (array1 is the name of an array of
numbers)
how does one know when to use Ctrl Shift Enter?
|