Page 1 of 2 12 LastLast
Results 1 to 10 of 17

How do you guys Chart random Large Values?

This is a discussion on How do you guys Chart random Large Values? within the Excel Questions forums, part of the Question Forums category; Hi Guys, I have a some data that is tracked on a weekly basis, I have created a bar chart ...

  1. #1
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,255

    Default How do you guys Chart random Large Values?

    Hi Guys,

    I have a some data that is tracked on a weekly basis, I have created a bar chart to show what the figure is on each day.

    Now there could be some random days where the figure is really high so that shows all the other values as really small and bar charts shrink and therefore cant see trends..

    My manager wants me to either show the abnormal values as 0 and have a comment somewhere or do some other technique that ensures all data can be visually viewable and be anle to spot trends...

    So what is the best way to do it and how shall i do
    it.

    If someone can come up with something that would be awesome..

  2. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    524

    Default Re: How do you guys Chart random Large Values?

    Perhaps this, by Jon Peltier, will help.

    http://peltiertech.com/chart-a-wide-range-of-values/

  3. #3
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,255

    Default Re: How do you guys Chart random Large Values?

    Thank you

    For me - im trying to see a trend without the odd occasions of these high values. Which way would becthe best way to do it?

    It mentions about fading and amending axis but it doesn't show how to get this fade..

    If its a case off amending the axis manually, id like to amend it via code so its more robust

  4. #4
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,375

    Default Re: How do you guys Chart random Large Values?

    If you want to totally ignore the abnormally high values, you can use a helper column to do so. Suppose my X values are in A2:Ax (x=last row), and my Y values in B2:Bx. In C2 I would put a formula like
    =IF(B2>$D$1,na(),B2)
    where $D$1 contains an arbitrary cut-off value that defines "abnormally high", and I would fill it down to cell Cx.

    Now plot column C instead of column B. The axis will scale automatically based on the numbers in column C, which are not abnormally high.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  5. #5
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,375

    Default Re: How do you guys Chart random Large Values?

    Alternatively you could follow the procedure in my comment to the above blog post.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  6. #6
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,255

    Default Re: How do you guys Chart random Large Values?

    Hi Jon,

    I have left a comment on the site (please see my comment below)
    Hi Jon, thank you for your input on the charts. In my case, i may have some days that could be abnormally high or just higher than normal. With this in mind, the smaller more common values are shown very small. I have used the LOG base 10 to show all the values but like you have advised, the smaller values look like its half of the big values. I guess the best way to do it in my case is to show the larger values off the scale shaded like you have done and show the smaller values (SHOW PART OF THE DATA EXAMPLE fits best i think to show a trend of what really is happening on a day on day basis).
    How can i add this shaded part for larger values and ensure the axis is updating correctly just encase i get a stupidly high value 1 day?

    It would be nice to graph the the small or common values and show the axis based on that and then show the larger values off screen with it shaded just like you have done in the SHOW PART OF THE DATA example.

    I dont know how to do it though

  7. #7
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,375

    Default Re: How do you guys Chart random Large Values?

    Did you read my latest comment? I describe one approach that you could use.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  8. #8
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,255

    Default Re: How do you guys Chart random Large Values?

    Hi The last bit is referring to Think Further Out of the Box - is this the bit your referring to?


  9. #9
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,375

    Default Re: How do you guys Chart random Large Values?

    Click on the link (repeated here). This is a comment I added to my post, after your comment. There is a quick step-by-step protocol you can follow.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  10. #10
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,255

    Default Re: How do you guys Chart random Large Values?

    the latest comment shows this ([…] document.write(''); Perhaps this, by Jon Peltier, will help. Chart a Wide Range of Values - Peltier Tech Blog […])

    and just goes back to the beginning off the page

Page 1 of 2 12 LastLast

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
  •  


DMCA.com