Dynamic x axis break based on values

[NickvdB]

Hi,

I have a waterfall graph in which I can select a team. Some teams have large budget and some have smaller budgets. Is there a possibility (preferably not vba) to have the x axis automatically break at a certain point? For instance 80% of budget amount? So y axis would start at 80K for a team with a budget

Thanks

 

[NickvdB]

Wow, luv the bar chart thinking!!! Looks great and seems perfect. Well have a look tomorrow. Thanks!!!

 

[felixstraube]

Ok. Let me know

 

[NickvdB]

Yes getting there!!
Strange thing remaining is the mismatches on the amounts for the deviations. In the chart Indexation is -20 while you can see it should be +10

 

[felixstraube]

Here is a working file for you to download:

Waterfall chart with Dynamica Y axis.zip

Waterfall chart with Dynamica Y axis.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Team 1	Team 2		Selected team		Selected data (data label)	90% of min	Totals - 90% of min	Closing value	Opening value		max	min	Pos dif	Neg dif	Label helper column
2	Annualized 2024	1450000	725000		Team 1		1,450	1,260	190,000.00	190,000.00	0.00		190,000.00	-	190,000.00	0.00	144000
3	Budget 2024	1400000	700000				1,400		140,000.00	140,000.00	0.00		140,000.00	-	140,000.00	0.00	144000
4	Merit	50000	25000				50		50,000.00	190,000.00	140,000.00		190,000.00	140,000.00	50,000.00	0.00	144000
5	Vac Fact 2024	50000	25000				50		50,000.00	240,000.00	190,000.00		240,000.00	190,000.00	50,000.00	0.00	144000
6	Replace	125000	62500				125		125,000.00	365,000.00	240,000.00		365,000.00	240,000.00	125,000.00	0.00	144000
7	Growth	125000	62500				125		125,000.00	490,000.00	365,000.00		490,000.00	365,000.00	125,000.00	0.00	144000
8	Transfer	125000	62500				125		125,000.00	615,000.00	490,000.00		615,000.00	490,000.00	125,000.00	0.00	144000
9	Inflation	60000	30000				60		60,000.00	675,000.00	615,000.00		675,000.00	615,000.00	60,000.00	0.00	144000
10	New	45000	22500				45		45,000.00	720,000.00	675,000.00		720,000.00	675,000.00	45,000.00	0.00	144000
11	Savings	-80000	-40000				-80		-80,000.00	640,000.00	720,000.00		720,000.00	640,000.00	0.00	80,000.00	144000
12	Others	50000	25000				50		50,000.00	690,000.00	640,000.00		690,000.00	640,000.00	50,000.00	0.00	144000
13	Budget 2025	1950000	975000				1,950		690,000.00	690,000.00	0.00		690,000.00	-	690,000.00	0.00	144000
14	Target 2025	1635000	817500				1,635		375,000.00	375,000.00	0.00		375,000.00	-	375,000.00	0.00	144000
Sheet1

Cell Formulas
Range	Formula
G2:G14	G2	=Team_Data
H2	H2	=MIN(G2:G3,G13:G14)*0.9
I2:I3,I13:I14	I2	=G2:G3-$H$2
M2:M14	M2	=MAX(J2:K2)
N2:N14	N2	=MIN(J2:K2)
O2:O14	O2	=IF(I2>=0, M2-N2, 0)
P2:P14	P2	=IF(I2<0, M2-N2, 0)
Q2:Q14	Q2	=MAX($M$2:$N$14)*0.2
I4:I12	I4	=G4:G12
K4:K12	K4	=J4-I4
J2:J3,J13:J14	J2	=I2
J4:J12	J4	=J3+I4
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Team_Data	=INDEX(Sheet1!$B$2:$C$14, 0, MATCH(Sheet1!$E$2, Teams, 0))	G2
Teams	=Sheet1!$B$1:$C$1	G2

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=Teams

In cell E2 you select the team you want to graph. Columns with green headers are the ones used in the graph.

Here i show you what the graph actually looks like without "hidding" some data.

First column sets the height of the actual data to graph, then comes the data. Positive data (increase) is in one column and negative data (decrease) in anothes so colors can be different. And finally a stacked bar chart doesn't allow you to add data labels above the highest value, so we use another hidden data column where we show the data labels in the middle.

 

[NickvdB]

Here is a working file for you to download:

OMG!!! Thank you so much for all the work and for educating me. Very much appreciated!!

 

[felixstraube]

Thanks for the feedback. I'm glad I could help.