How can I make array formulae shorter in a macro?

Posted on 2012-08-31
Last Modified: 2012-09-01
Can I reduce the length of "Array Formulae" to work in a macro or split into smaller portions. I have several formulae in the attach file that have a length longer than 255 characters as a result they fail. Could someone assist by improving this code. At this time, I'm only working with row (5) as a test with the rest of the rows having formulae in the colored cells that return the results required.

Any assistence would be appreciated.

Question by:user2073
    LVL 33

    Expert Comment

    Which worksheet are these formulas to go on?

    Author Comment

    The formulas are to be placed in worksheet "Nambucca_October 2011" in row (5) in the colored cells. If look in row (6) you'll see working formula that are being used as a sample.
    Sorry, if I wasn't clear on this.

    LVL 50

    Accepted Solution

    In your longest formulas you are repeating the formula twice just to make a zero into a blank, do you really need to do that? If that's just for display purposes you can simply format the cells to display zeroes as blanks, alternatively try using this version for the first problem formula

    "=IFERROR(1/(1/INDEX(ws1!R4C1:R10000C17,MATCH(RC[-14]&TEXT(EDATE(R2C2,-1),""mmmmyyyy""),ws1!R4C4:R10000C4 & ws1!R4C3:R10000C3 & ws1!R4C2:R10000C2,0), MATCH(""Data1"", ws1!R2C1:R2C17,0))),"""")"

    Open in new window

    Note I changed the date part, if your R2C2 date is January you'll get the wrong year, so I changed that......

    regards, barry

    Author Closing Comment

    Barry your the man.

    Your code works perfectly, many Thanks.
              = IFERROR(1/(1/INDEX(w
    In the above portion of codes "1/(1/INDEX" can you tell me what this means in plan english?

    I appreciate the efforts from this site.
    LVL 50

    Expert Comment

    by:barry houdini
    We want to force an error when the INDEX formula returns zero, so if you divide 1 by the result of the INDEX formula then you get a #DIV/0! error where the result is zero, but if, for example, the result of INDEX is 10 then 1/(1/10) = 10 so if INDEX returns any other number the result will be unchanged.

    IFERROR will then convert the #DIV/0! errors to blanks

    regards, barry

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now