patboule.com

I was helping a friend get a complicated array formula working with Excel the other day. She had a sample formula taken from the Web and was pulling her hair out trying to get it working. Now, I know a fair bit about Excel, but I’d never worked with array formulas  before.

In short – they’ll act on a a whole range of cells in a single formula. When you’re done editing them, you hit Ctrl-Shift-Enter to show they are array formulas, and Excel adds in some {} brackets around it. That’s also why they’re called CSE formulas.

Despite this – we had the formula working in pretty short order, cause we applied a bunch of Troubleshooting 101 techniques to the problems.

1. Break the problem down into component parts.
2. Get something working, anything!
3. Reduce the range so a person can see it
4. Check as you go – force some matches
5. Finally, focus on the hard stuff
6. Don’t forget the remnants
7. One, two, infinity. Expand your solution.

Here’s the problem in excel speak – for every row in a huge long range, we were trying to sum everything that matched three criteria:

1. a shopping cart number (column I)
2. an approval number (col J)
3. a month (hardcoded)
4. an invoice column (AG)

=SUM((‘[KSB1-2009-2010.xls]Sheet1’!$AA$2:$AA$4006=I4)*(‘[KSB1-2009-2010.xls]Sheet1’!$AL$2:$AL$4006=J4)*(‘[KSB1-2009-2010.xls]Sheet1’!$AO$2:$AO$4006=”Jan”)*(‘[KSB1-2009-2010.xls]Sheet1’!$AG$2:$AG$4006))

Here’s what we did:

• Break the problem down into component parts.

We’re trying to do a sum based on number of multiplications based on matches within different columns. If there’s a match, it turns up as a “1”…so it’s like doing a conditional sum on all rows that match. Looks doable – let’s tackle it in parts. Let’s try an easy sum (non-array formula) first, just to get the juices flowing.

Our first problem was that the template she was using had every cell formatted as text – which can cause you to poke your eyes out if you try and type a formula in it. In this version of excel, even changing the cell format to general afterwards won’t kick your formula into working. You need to change format, then delete and retype your forumla. Ouch. I was starting to understand her frustration.

• Get something working, anything!

Next problem – get ANY array formula working. Neither of us had worked with them before – let’s try a simple one just summing ANYTHING before boiling the ocean.

So we started with a simple one term array formula summing everything that matched I4 – it gave us a result – and we checked it against a pivot table so we knew it worked.

• Reduce the range so a person can see it.

Now we added the other terms back in – but got a bunch of N/A results. Hmm. When we went into Evaluate Calculation pulldown, all we saw was a long series of 0’s and N/As. Time to reduce the range from a 4000 row range to something manageable that a human can get their head around – like 6.

So we changed our ranges down to 6.

We still got the N/A result – but we could now see the problem was with the second term in the equation – the I4 lookup. So we took that term out. We now saw numbers – but they were all zeros.

• Check as you go – force some matches

Once we had the range down to 6, and the wonky term taken out, we were just seeing zeros – no surprise there were no 2 term matches in the 6 rows of data. So we edited this data to fake it – just as a check. Ok the reduced match is working.

• Finally, focus on the hard stuff

Now the only thing left was the wonky I4 match – which turned out to be the result of an evil “number stored as text”, which in Excel looks like text, but does not act as text when used as part of a formula. Even the Excel MVPs don’t write much about the subject – but Excel must do something funny internally to these cells just in case users try and recast them as numbers later on.

However – we were able to force the text to be treated as text, for formula purposes, with a formula called, oddly enough: TEXT.  As my old computer lab buddy Art used to say, “Funny how it’s always the last thing you try that works”.

• Don’t forget the remnants

The above solution worked for most of our approval numbers, which were integers, but we needed to wrap that statement in an IF to handle the remaining numbers which were one digit decimals – because the text function was actually pretty harsh about how it was going to display different numbers.

=IF((LEN(I4)>4),TEXT(I4,”?.?”),TEXT(I4,”?”))

• One, two infinity  – expand your solution to cover the whole problem

Time to put a bow on the formula – re-expand the ranges, fix the master data you changed so you’d have an easy match. The final thing we did was anchor the lookups so that when the formula got copied over to sum different things, it would still work, and de-referenced the month to a header cell at the top of the column.

So it turned out to be 7 or 8 steps peeling the onion. But the onion is peeled, baby!

This entry was posted on Monday, May 3rd, 2010 at 11:50 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.