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 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.
Excel is a registered trademark of the Microsoft Corporation.
