[an error occurred while processing this directive]

Can You Total That Diagonally For Me?

Devoted to impossible problems posed by real managers. These problems should make seasoned Excel veterans cringe. The title is a phrase coined by a great fellow named Eric from McKinsey & Company. With a wry sense of humor, Eric would greet all nearly-impossible requests with, "sure, and then can you total that diagonally for me?" Spreadsheet pros will chuckle as they recognize these simple sounding problems as ones without an easy solution.



Joe related this story via e-mail:
A particularly particular boss once asked a coworker of mine to "add a 4th dimension" to his graph. The coworker came to me for help, as the local MS Office guru.

I asked him to explain further, as sometimes people ask for specific solutions, when what they have is a more general problem. Boss man wanted a graph that displayed R&D project requests by month, department, and cost, AND by "credibility" - how likely the project was to succeed, in his opinion.

I sparked. The boss didn't want a true 4th axis, just a few levels of indication. I instructed the worker to plot a 3-d column chart (no problem), and then hand-format each column to a spectrum of colors. I recommended "True Blue", "OK-To-Go Green", and "Liar-Liar Yellow" as his spectrum. If the boss wanted more levels, he could make yellow into "Just OK" and add "Hotly Contested Orange" and "Incendiary Red"... but you get the picture.

The boss was delighted. The coworker was raised (in this boss' eyes) from trivial flunky to competent underling. And I got the satisfaction of watching from the safe sidelines. For a boss who will ask this sort of thing, and get it, is a dangerously fed beast, who will soon be hungry again...


Joe - Thanks for sending this great story. I agree that someone who makes a break through like this quickly goes from being a hero to regretful, as they quickly are overwhelmed with similar requests. . . But that is the point where they should hire
MrExcel Consulting to automate the beastly task!

For sharing this story (and actually a great charting idea), Joe will receive a highly collectible MrExcel 2002 Winter Olympic Calendar. If you have a similar tale of a real request issued by a real manager, send it to diag@MrExcel.com.


This problem was sent in by a reader in 1999, back before MrExcel.com became my full-time gig. Read the whole thread to find the eventually simple outcome.

The impossible problem for this issue was an actual problem posed to an analyst. He wrote asking how to calculate a "Binford Factor". I don't know if Mr Binford has a patent on the concept, so I'll change the name and what it is used for, but you will still get a feeling for the problem:
> I have to use a formula called the Binford Factor (??!) to calculate a
> number which indicates the patterns and frequency of events.
>
> The columns and rows signify the weeks and customers respectively. In a
> week in which a consulting engagement occurs, a number of days will be placed in the
> corresponding box. The numbers for each week in a 52 week period are put
> into blocks, any week that does not have an adjacent numer is a block -
> however if two blocks (containing 1 for example) sit side by side they are
> assumed to also form a block (as in the assumption that the consulting lasted from
> Friday to Monday) blocks of more than three '1's' would
> form the least amount of blocks...and so on.
>
> The Binford formula is (blocks * blocks)*sum of days
>
> Thus someone who had a prolonged contract would have one or two
> blocks of '5's' with a total of 40 days - (i.e. (2*2)*40) = 160
> and someone with fourteen contracts for no more than two days would
> have ((14*14)*21) = 4116
>
> You can see what I'm getting at. Please help!!
Well, my fellow guru's, what do you think? Keep in mind that MrExcel answers questions early in the morning, starting around 5 or 6AM. What a great way to wake up. How would you like to sign on and find this little gem waiting for you! Here is the answer I proposed.
This would have to be the most complex Excel problem that I have ever seen. I wonder if you are an Excel prankster who knows that this problem will cause my head to explode. But, I am guessing you are for real and you really have to try and do this.

You have customer name in Column A. Weeks 1 to 52 in columns B through BA. You need a way to place the number of blocks in a (possibly hidden) column BB. The sum of the number of days in BC is very easy. Then BD would calculate the Bradford Factor as =BB*BB*BC.

So, the trick is in calculating BB. A macro or macro-like function could quickly run through cells B through BA, figuring if the previous cell was filled and if not, adding to the number of blocks. Are you proficient with macros?

If not, another solution (big and ugly) is to have 52 new columns in cells BE through DD. The point of these 52 columns is to identify the start of a block only.

Formula in BE2, (a special case) is =IF(B2>0,1,0)
Formula in BF2 is =IF(AND(C2>0,B2<1),1,0)
Copy the formula from BF2 out to DD2.
In English, the formula in BF2 says: "First, was there a consulting engagement for any days in week 3? (C2>0) Second, was there not an engagement in Week 2? (B2<1) If both are true, AND( , ) we have the start of a block, so put a 1 here to count the number of blocks, otherwise put a zero here."

Now, with this monster block of 52 cells, you will have a 1 at each onset of a block of consulting engagements.

Cell BB2 counts the number of blocks with =SUM(BE2:DD2)
Cell BC2 counts the number of days with =SUM(B2:BA2)
Cell BD2 counts the Binford Factor (who is this Binford chap, anyway?) as =BB2*BB2*BC2


And, the happy ending is that our valued MrExcel reader got the project done and in on time. He wrote:
I never thought such a scribbled note in utter desperation could beanswered so clearly and promptly. Thank you.

I work for a charity and am the only person that knows anything aboutcomputers and so I am the mug that gets asked to do these sort of things.I would say that you've probably enabled me to speed my way, unhinderedthrough the echelons of promotion..but I work for a charity so....naaah!

I hope it didn't put your grey matter through too much pyrotechnic bother. Thanks again


Here is the actually simple answer, proposed by Chris, a reader several months later."I enjoy Mr Excel and you are usually pretty smart. Why didn't you just use this simple array formula to eliminate the 52 columns? Thanks to Chris who proposed =SUM(IF(C2:BA2>0,IF(B2:AZ2<1,1,0),0)). This works much better. If you are not familiar with array formulas, read the
CSE Formula tip for information on how to enter this formula.


Fellow Excel guru and all-around good-guy wrote in March 2002 to take exception with the title of this page. I've been using this "total diagonally" line for 10 years to get laughs, and never considered that there is a solution. Chip wrote,
Well, spreadsheet pros also know that simple sounding problems often have simple solutions, as long as the right tool is used. In this particular case of totally diagonals, the solution is quite simple, with an array formula.
=SUM((ROW(C11:E13)-ROW(C11)=COLUMN(C11:E13)-COLUMN(C11))*C11:E13)
As long as the range is square (same number of rows and columns) and all the cells are numeric, this will total diagonals. If the cells may contain text, you the longer formula below to test for numeric data
=SUM((ROW(C11:E13)-ROW(C11)=COLUMN(C11:E13)-COLUMN(C11))*IF(ISNUMBER(C11:E13),C11:E13,0))
Cordially,
Chip Pearson
Chip and I had a great conversation. Chip won a MrExcel T-Shirt for his contribution. If you ask anyone who has ever dealt with Chip, they all agree he is a super guy. Thanks to him for his contribution. Be sure to check out
Chip's excellent site!

David Hogg from Canada checked in during April 2002 with this solution to sum the other diagonal:
"Here it is as an array (CSE) formula: =SUM( (COLUMN(E9:G11) + ROW(E9:G11) = COLUMN(E11) + ROW(E11)) * E9:G11 )
This would also work by replacing E11 with any other cell on the other diagonal.
"
Thanks to David for his contribution!


Well, my fellow gurus, do you have a horror story to tell? Send your story to MR. EXCEL.

Return to the Mr Excel Home Page.

Thanks for visiting Mr Excel.com! Proudly serving the web since November 21, 1998. You are visitor # .

Last Updated April 23, 2002. All contents copyright 1998-2008 by MrExcel.com.

Send questions to ask@MrExcel.com

Excel is a registered trademark of the Microsoft Corporation.