Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Scott Ehrlich
 
Posts: n/a
Default Charting challenge/query

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing MM:SS From Tab Delimited File and Charting Bryan Charts and Charting in Excel 4 July 28th 06 04:14 PM
Multiple rows of data on a single axis (charting) ramseysgirl Charts and Charting in Excel 8 December 29th 04 07:00 PM
Business Case Charting Phil Hageman Charts and Charting in Excel 4 December 17th 04 12:51 PM
Charting data ranges that change mikelee101 Charts and Charting in Excel 2 December 17th 04 12:07 AM
Charting time? help please... Gustavo Monteverde Charts and Charting in Excel 1 November 28th 04 05:53 AM


All times are GMT +1. The time now is 01:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"