Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Need Help with Charting or statistics or something

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Afraid you are over my head here. . .


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

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    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.

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can also take a look at the Data Analysis Add-in. There are a lot of cool stat wizards.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

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


    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

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •