# Array formulas in Excel for Mac

1. ## Array formulas in Excel for Mac

I'm sorting linguistic data, and I need to count cells within a range that do NOT contain ANY of four specific entries. In addition, I need to check these against one further criterion in another column before counting them. E.g, it would be like counting lines that have an x in column A, AND do NOT have an a, b, c, OR d in column B. (And better than a simple count, I'd love to have an array of 1s and 0s telling where the conditions are met.)

I am completely new to array formulas in Excel (though have some background in other types of programming). Just as a test to see how the array calculations work, I've been trying this formula.

I set up a 'testrange' that contains the following entries:

``Please Login or Register  to view this content.``
Then I'm trying to run this formula, using ctl-shift-enter:

[code]
{=IF(testrange="B",1,0)}
[\code]

I expected to get an array that looks like this:

[0
1
0
0
0
0]

Or failing that, at least a 1, because the range contains a match.

What I get instead is just a scalar 0. If I change the formula to read

[code]
{=IF(testrange="A",1,0)}
[\code]

then I get a 1 as output. In other words, it appears to test only the first cell in the range. Is this a strange MAC issue, or just how this command works? I tried duplicating the formula alongside each of the entries, but I still got only zeros. Is anyone able to explain how I could get an array showing where the condition is true as output? In the "real world" I'm working with about 1500 lines of data spread across 6 worksheets, so I want to know what I'm doing before I start trying to interpret output.

Many thanks for your help, and as this is my first post, please graciously let me know if it's over/under detailed, or unclear!

2. ## Re: Array formulas in Excel for Mac

But have you selected enough cells before entering your formula? You shall select a range of say F1:F6 and having it selected write the formula, then array commit (on MAC it's I think CMD-SHIFT-ENTER)

3. ## Re: Array formulas in Excel for Mac

Hi, Kaper, thanks for your reply. I do think I've accounted for the need to test the six values in the range by defining a named range, 'testrange', that includes F1:F6 (as per your example), and referring to that range in the formula. If I actually select/highlight the range before writing the formula, the selection clears when I click on another cell to enter the formula. Is there something I'm missing?

4. ## Re: Array formulas in Excel for Mac

You refer to defined range which is input range to formula.

I'm talking about output range (where formula is written).

It is not formula in F1 copied down. You select all cells in output range write formula (either in formula bar or directly in first cell) and array commit. Now whole output range is filled with the output of the formula, and is treated as one vector. Thus, it cannot be chhanged manually - try to delete F3 in attachment for instance.

5. ## Re: Array formulas in Excel for Mac

Aha! That did it!

I got the output

0
1
0
0
0
0

in the range I selected.

Thank you!! Now for further experimentation... but I will close this thread as that answered the specific question.

6. ## Re: Array formulas in Excel for Mac

There are currently 1 users browsing this thread. (0 members and 1 guests)