[an error occurred while processing this directive]

- Create a 4-D Chart
- The meandering tale of the Binford Factor
- Chip Pearson's solution to the title of this page!

A particularly particular boss once asked a coworker of mine to "add a

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

> 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!!

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

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,

=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

"

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.