+ Reply to Thread
Results 1 to 6 of 6

Array formulas in Excel for Mac

  1. #1
    Registered User
    Join Date
    03-12-2018
    Location
    Louisville, KY
    MS-Off Ver
    Excel for Mac ver. 15.32
    Posts
    3

    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. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    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)
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    03-12-2018
    Location
    Louisville, KY
    MS-Off Ver
    Excel for Mac ver. 15.32
    Posts
    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. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    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.
    Attached Files Attached Files
    Last edited by Kaper; 03-12-2018 at 02:26 PM.

  5. #5
    Registered User
    Join Date
    03-12-2018
    Location
    Louisville, KY
    MS-Off Ver
    Excel for Mac ver. 15.32
    Posts
    3

    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. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Array formulas in Excel for Mac

    Glad it worked

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 01-01-2016, 03:44 PM
  2. Question about using Evaluate with an array of formulas not an array formula
    By Mr_Bill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2014, 03:26 PM
  3. Array formulas in excel
    By Knocknaboula in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2013, 11:53 AM
  4. Ctrl+Shift+Enter (CSE) array formulas in VBA using differing formulas
    By officeguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2012, 04:31 PM
  5. Replies: 6
    Last Post: 12-30-2008, 06:52 AM
  6. Array Formulas in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-15-2007, 03:35 AM
  7. Excel array formulas
    By Les Gordon in forum Excel General
    Replies: 1
    Last Post: 09-03-2005, 12:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1