View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default IF Formula to go through every line of array

Hi!

Array formulas are different.

Normally when you type a formula like: =SUM(A1:A10), you hit the ENTER key
to place the formula in a cell.

With array formulas there is a special sequence of keys that you MUST use.
Type the formula then hold down both the CTRL key and the SHIFT key then hit
ENTER. When done properly you'll see in the formula bar that the formula is
enclosed in squiggly braces { }. The braces denote an array formula. You
MUST use the key combination, you can't just type in the braces. Also, if
you edit an array formula it MUST be re-enteed as an array.

Also, when you say "Match(1," what are referring to with the "1"?


When these arrays are multiplied together:

(A1=D$1:D$3)*(A1<=E$1:E$3)

they will return either a 1 if the condtion(s) is true or a 0 if the
condition(s) is false. It would look something like this:

MATCH(1,{0;1;0}

Match 1 matches the 1 that's in the second position. This matching position
(2) is then passed to the Index function and the result of the formula is
the second value that is indexed in the Index function:

=INDEX(A1:A10

The value that would be returned is the value in A2 since it's in the second
position.

Biff

"Leibtek" wrote in
message ...

Enter this formula in B1 as an array using the key combo of

CTRL,SHIFT,ENTER:

I'm not sure how to do that...


Also, when you say "Match(1," what are referring to with the "1"?

Thanks,
Leibtek


--
Leibtek
------------------------------------------------------------------------
Leibtek's Profile:
http://www.excelforum.com/member.php...o&userid=32287
View this thread: http://www.excelforum.com/showthread...hreadid=520424