How do you guys Chart random Large Values?

[mahmed1]

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

 

[mahmed1]

This is the steps i have followed

Here is the data

Date	Values
01/01/2016	49
02/01/2016	31
03/01/2016	578
04/01/2016	19
05/01/2016	37
06/01/2016	7
07/01/2016	34
08/01/2016	949
09/01/2016	36
10/01/2016	21
11/01/2016	41
12/01/2016	50
13/01/2016	20
14/01/2016	15
15/01/2016	36
16/01/2016	7
17/01/2016	15
18/01/2016	11
19/01/2016	46
20/01/2016	49
21/01/2016	2
22/01/2016	44
23/01/2016	37
24/01/2016	298
25/01/2016	26

<colgroup><col><col></colgroup><tbody>
</tbody>

I have created a column chart

on the vertical axis - i clicked logarithmic scale and left it as 10

This now shows the axis from 1 to 1000

This is where i have got to so i guess now im trying to just get the axis to be more consistent with the data and show the large values off screen with the shaded part..

Im sorry but i cant seem to find the step by step guide to get round this with the link you posted

 

[mahmed1]

Hi Jon

Your post is showing now thank you - i will give that a go

Thank you

 

[mahmed1]

Thank you Jon - your example works a treat to show the high values shaded

Is there a way or best fit formula to calculate this arbitrary cut-off value? It would be awesome to look at the data and be able to calculate an arbitary cut off figure based on whats outside the norm

It would also be able to show the date values more nicely as the more the data grows - the date range looks congested

 

[mahmed1]

The reason i say this is because for different teams - that arbitary figure could be different because certain teams will have more more common valus and could have some random abnormal values

ie

Team As figure could be like this

			120
	Team	Sales	Huge	Normal
01/01/2016	A	49	#N/A	49
02/01/2016	A	31	#N/A	31
03/01/2016	A	578	120	#N/A
04/01/2016	A	19	#N/A	19
05/01/2016	A	37	#N/A	37
06/01/2016	A	7	#N/A	7
07/01/2016	A	34	#N/A	34
08/01/2016	A	949	120	#N/A
09/01/2016	A	36	#N/A	36
10/01/2016	A	21	#N/A	21

<colgroup><col span="2"><col span="3"></colgroup><tbody>
</tbody>

Team Bs figure could be like this

	Team	Sales	Huge	Normal
01/01/2016	B	120	#N/A	120
02/01/2016	B	400	200	#N/A
03/01/2016	B	300	200	#N/A
04/01/2016	B	166	#N/A	166
05/01/2016	B	198	#N/A	198
06/01/2016	B	95	#N/A	95
07/01/2016	B	28	#N/A	28
08/01/2016	B	158	#N/A	158
09/01/2016	B	268	200	#N/A
10/01/2016	B	50	#N/A	50

<colgroup><col span="2"><col span="3"></colgroup><tbody>
</tbody>

I am having a drop down to load up the values depending on what team i select and then the chart will update based on the team selected

Now the chart you gave an example of works a treat but it would be even better if i can somehow calculate this arbitary figure because i have over 50 teams therefore will have to go through each team and change the arbitary figure

If i can somehow get this - i will use a macro to change the axis max value to the arbitary figure for that team

 

[mahmed1]

This is the code im using to change the axis value so its just a case of calculating this arbitary figure to what fits best

Sub ChangeAxisValue()


Dim ws As Worksheet
Dim MaxVal As Long


Set ws = Worksheets("Sheet2")


MaxVal = ws.Range("E2").Value


With ws.ChartObjects("AQChart").Chart.Axes(xlValue)
   .MaximumScale = MaxVal
   .MinimumScale = 0
End With


End Sub

 

[Jon Peltier]

If I look at the values for Team A, it looks like most values are below 50 except for two very large values. You could make a plot with a maximum scale of 60 or so, and it would work fine.

If I look at the values for Team B, there is no clear-cut level where you can cut off the tall bars. There are two values of 50 or less, two more around 100, two more near 150, one near 200, one near 250, one near 300, and the tallest is around 400. You should plot Team B's data normally without any of the cut off tall values I wrote about.

And I don't think it's really reasonable to plot the two teams on one chart, since their scores are so different.

 

[mahmed1]

Thank You