Help with data not getting plotted

The following quote is posted on behalf of a client using Windows and
Office 2003 Standard. Please email responses directly to me, too,
since I do not read this group much.

Any help/insight would be most appreciated. I'll do my best to relay
any questions people have and bring back any answers to said questions.

<quote>
The Goal:
In an effort to come up with a new way of looking at organizational
structure, I employed Excel's 'Donut Chart' function. My goal was to
have the head of the organization in the middle of the chart and then
that person's direct reports represented by the inner-most ring of pie
slices. Those people's direct reports would be the next ring of slices
and so on until the outermost ring of pie slices represents those
individuals lowest down in the organizational structure with no
supervisory responsibilities.

Ideally, each slice of the donut chart would be labeled with the
person's name and job description. The largest facility that I was
looking at was approximately 3,000 people. You may wonder why I would
seek such detail when the names and information would be too small to
read when the chart is printed out. The answer is that I have had
individuals in engineering print out very large versions of the chart
(several feet square) that would make each sliver of data legible. The
goal being that someone could spread out this 'poster' and see his
entire organization and the flow thereof in a single view.

Source Data:
I arranged the hierarchical data within excel such that each individual
was a row of data. Each data series represented a level of the
organizational structure. For example: the facility VP may have been a
level 4 and a product assembler at that facility a 9 or 10, with
managers, supervisors, etc. layered in between. The structures varied
somewhat from facility to facility. I arranged the data on the
spreadsheet by offsetting the rows for each particular level. As a
result, the data range for level 5 individuals would go from, say, D1 to
D3000 and just pick up those individuals on that level. The next
series, level 6, would be E1 to E3000 and so on. In order to synch up
all the layers of slices, the values for each data point had to be the
number of people at the lowest level who report to that individual. For
example: if a manager (1 person) has 5 direct reports and each of those
5 individuals has five direct reports, then the value for that manager
needs to be 25. The value of each of his direct reports would be 5 and
the value of each of their direct reports would be 1, assuming that no
one reported to them.

I also arranged for the chart to represent blank slices if someone
higher up in the organization had no direct reports (example: a senior
level engineer with no supervisory responsibilities). I did this by
inserting dummy inputs (value of 1) in the series for each lower level.

The Problem:
The problem arose not in arranging the data inputs for the chart but in
excel.s creation of the chart itself. Given the finite nature of a
circular chart, excel appeared incapable of representing every data
point. It is my belief that the source of the problem was the sharp
contrast in values that would be presented in any given data series. For
example: a single production manager may have 500 people reporting to
him, either directly or indirectly. That same manager may be a 'level
6' within the organization. Working right alongside that manager may be
a senior manufacturing engineer who is also a level six. That senior
engineer may have a single person reporting to him. Such contrasts
within the organizational structure were frequent (part of the reason we
wanted to take a look at it). What would happen is that excel would
recognize the manager's slice with a value of 500 but that the single
slice of the senior engineer with a value of one would not even show up
on the chart.

The Solution:
I spent a great deal of time looking at excel and its limitations. I
was unable to identify a clear statement within its limitations that
legitimized this failure. Nonetheless, I moved on. I took several
corrective actions to complete the project within excel. The first
action was to re-focus the scope of the organizational structure that I
represented. For instance, rather than start with the Facility Head in
the center of the chart, I started with the Director of Manufacturing,
who worked for the guy who worked for the facility head. This limited
the number of overall individuals from 3000 down to 2200. The other
action I took was to ignore all individuals who had no supervisory
responsibilities. This essentially removed the outermost ring of the
chart, which is a good thing because excel proved entirely incapable of
accurately depicting the hundreds of tiny slivers who were low in the
organization. Yet excel still was not representing every data point
that I identified. The last action I took was to 'fudge' the data
inputs. If a data point of, say, 1, proved too small to show up on the
chart then I would change that input to perhaps a 5, or whatever value
was necessary to have it show up. To offset this addition of 4 units, I
would subtract 4 from another point within the series, preferably one
with a large number like 500 so that the scale would remain generally
intact.

The end result was a finished product less comprehensive and more labor
intensive (keeping all the fudges straight was a pain in the neck) than
originally planned; a compromise.
</quote>

In article <#[email protected]>,
[email protected] says...
> The following quote is posted on behalf of a client using Windows and
> Office 2003 Standard. Please email responses directly to me, too,
> since I do not read this group much.
>
{snip}
>
Maybe, you should point your client to the newsgroup. That way s/he
can get the benefit of direct interaction with those solving the
problem.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions