set Chart Axis in relation to zero (for negative & positive numbers)

[Eitan l]

Hi,​

i have a column chart that present profit, the value can be negative and/or positive (depending on a slicer selection of a factory) .​

the problem is that the auto selection in the chart presents the data in a very narrow scale (not from zero) and present big differences for very small variations (for example 90 and 91 and 91.5 presented like we have a major increase).​

choosing the minimum value as 0 will not work because sometimes one of the values is negative.​

is there another way to make the chart work around the zero.​

thank you in advance​

 

[Hui]

Eitan I

Firstly, Welcome to the Chandoo.org Forums

Secondly the Forums here are broken into Practical areas: This question should be in the
Discuss Data Visualizations and Charting

area

To do what you want you need to use a Stacked Column chart
The first series will set the most negative value you need
The second series will be the cumulative difference between the lowest value and your data
the third series will be the difference between the maximum value and your data
See the attached file

 

[Eitan l]

Hi, thanks for your reply and greeting.
this is a good idea but if i understand correctly the chart will always show negative and positive numbers (even if all the values are positive the chart will still show the minus range of the chart).

 

[Hui]

You can use formulas to derive the Min / Max values to whatever criteria you want

if(Min Range <0, -20%, 0)
if(Max Range <0, 0, 20%)
etc

 

[Eitan l]

sound good, thanks

 

[Eitan l]

i could not manage to do as you said, where can i put the formula in order to create the borders?
if i put it in the stack chart then i will allways have a small minimum (or the axis will jump back to the the original range)

 

[Hui]

In the yellow Min/Max cells that I used in your file where the +/-20% values were
You may want to increase the max/min by a fixed amount eg: Min()*1.15 etc

Refer:

 

[Eitan l]

In the example you sent the Axis starts from 21%

 

[Hui]

Adjust the series that refers to the Top line of data to be Series 1, not series 3

 

[Eitan l]

Beautiful, thanks