Need Help with Charting or statistics or something

cls · Apr 8th, 2002, 03:12 PM

Greetings,

I have a worksheet with several thousand rows of data detainling real estate companies that listed or sold properties within a given time period.

I have used a pivot table to summarize this data so I could get a count of just how many properties each company listed or sold and the percentage of the whole for each.

The results are that one company stands clearly above the rest with 13% of the total sales or listings; another 15 or so fall into the 1% to 10% range and then there are hundreds in the less than one percent range.

I have been asked to create a line chart that will clearly illustrate that a few companies are responsible for most of the listings and sales.

Easy enough except that the HUNDREDS of >1% companies are just too much to handle - I wind up with a chart that can not be read.

So I tried grouping the >1% folks, but then that one group winds up with 48% and makes the top guys look like they are doing nothing at all. Not what is being requested.

I can get something more reasonable from a pie chart - It just does not have the impact.

Is there a way to do this in a line or bar chart that I simply am not seeing. Or is the pie going to have to be the answer?

Thanks for your thoughts.

Aladin Akyurek · Apr 8th, 2002, 03:23 PM

You could apply a transformation (LN or SQRT) to the target data first, then chart.

GregPascal · Apr 8th, 2002, 03:44 PM

Why don't you simply cut off the group with the "top ten" firms who sold properties and state on the chart that, while the sum of the rest was significant, the fact remians that no simgle firm was significant.

cls · Apr 8th, 2002, 03:50 PM

Afraid you are over my head here. . .

Quote:

On 2002-04-08 14:23, Aladin Akyurek wrote:
You could apply a transformation (LN or SQRT) to the target data first, then chart.

cls · Apr 8th, 2002, 03:51 PM

That may be the most reasonable option for where my skill base is.

I just wanted to make sure I was not missing somethin obviously simple.

Thank you for your time.

Quote:

On 2002-04-08 14:44, GregPascal wrote:
Why don't you simply cut off the group with the "top ten" firms who sold properties and state on the chart that, while the sum of the rest was significant, the fact remians that no simgle firm was significant.

Al Chara · Apr 8th, 2002, 04:02 PM

You can also take a look at the Data Analysis Add-in. There are a lot of cool stat wizards.

Aladin Akyurek · Apr 8th, 2002, 04:18 PM

Quote:

On 2002-04-08 14:50, cls wrote:
Afraid you are over my head here. . .

Quote:

On 2002-04-08 14:23, Aladin Akyurek wrote:
You could apply a transformation (LN or SQRT) to the target data first, then chart.

Lets say that you have a bunch of numbers in A1:A10 you want to chart.

In B1 enter: =LN(A1)

and copy down till the 10th row.

You could then use the values in B1:B10 instead of the original values in A1:A10. You might take this LN transformation as smoothing.

Aladin

cls · Apr 8th, 2002, 05:54 PM

Quote:

On 2002-04-08 15:18, Aladin Akyurek wrote:

Quote:

On 2002-04-08 14:50, cls wrote:
Afraid you are over my head here. . .

Quote:

On 2002-04-08 14:23, Aladin Akyurek wrote:
You could apply a transformation (LN or SQRT) to the target data first, then chart.

Lets say that you have a bunch of numbers in A1:A10 you want to chart.

In B1 enter: =LN(A1)

and copy down till the 10th row.

You could then use the values in B1:B10 instead of the original values in A1:A10. You might take this LN transformation as smoothing.

Aladin

I will play with that in a bit and see what difference it makes to my data. Thank you for your time explanation.

cls

Apr 8th, 2002, 04:02 PM	#6
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	You can also take a look at the Data Analysis Add-in. There are a lot of cool stat wizards. __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 8th, 2002, 03:12 PM	#1
cls New Member Join Date: Apr 2002 Posts: 5	Greetings, I have a worksheet with several thousand rows of data detainling real estate companies that listed or sold properties within a given time period. I have used a pivot table to summarize this data so I could get a count of just how many properties each company listed or sold and the percentage of the whole for each. The results are that one company stands clearly above the rest with 13% of the total sales or listings; another 15 or so fall into the 1% to 10% range and then there are hundreds in the less than one percent range. I have been asked to create a line chart that will clearly illustrate that a few companies are responsible for most of the listings and sales. Easy enough except that the HUNDREDS of >1% companies are just too much to handle - I wind up with a chart that can not be read. So I tried grouping the >1% folks, but then that one group winds up with 48% and makes the top guys look like they are doing nothing at all. Not what is being requested. I can get something more reasonable from a pie chart - It just does not have the impact. Is there a way to do this in a line or bar chart that I simply am not seeing. Or is the pie going to have to be the answer? Thanks for your thoughts.

Apr 8th, 2002, 03:23 PM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	You could apply a transformation (LN or SQRT) to the target data first, then chart.

Apr 8th, 2002, 03:44 PM	#3
GregPascal New Member Join Date: Apr 2002 Posts: 6	Why don't you simply cut off the group with the "top ten" firms who sold properties and state on the chart that, while the sum of the rest was significant, the fact remians that no simgle firm was significant.