How do you guys Chart random Large Values?

Thanks:  0
Likes:  0

# Thread: How do you guys Chart random Large Values?

1. ## 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. ## 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. ## 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?

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

4. ## 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.

5. ## Re: How do you guys Chart random Large Values?

Alternatively you could follow the procedure in my comment to the above blog post.

6. ## 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. ## Re: How do you guys Chart random Large Values?

Did you read my latest comment? I describe one approach that you could use.

8. ## 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. ## 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.

10. ## 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

## User Tag List

#### Posting Permissions

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