Data labels on the outside end of error bars without overlapping?

C

Crucis

New Member
Joined
Aug 31, 2017
Messages
5
g8abWk
Dear all,

I have a recurrent problem when making column/bar charts with error bars and data labels. Whenever I add data labels to the outside end, these labels will always overlap the error bars (please see image). Is there any automatic solution to position the data labels on the outside end of the error bars (instead of the column end)? Dragging each data label manually is annoying and time consuming when dealing with a lot of data.

Thanks in advance for your help! :)

4SlvCta.png
g8abWk
 
Sort by date Sort by votes
Hey Phil,

I have just tested it and this last piece of code knocked it out of the park! Thank you very much!!!
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi Phil - your solution is from a very old post, but it looks like it will completely solve my problem...have been struggling with this for days. My data is structured differently however, and I haven't been successful in adjusting the code to run from my data structure vs. the data being in the 4 columns as specified. Wondering if you'd be willing to guide me through modifying your code to meet my specific needs? Thanks is advance - very much appreciated!

Steve
 
Upvote 0
Post a few rows of your data here and explain how it is incorporated into your graph and I will see if I can figure it out.
 
Upvote 0
Thanks so much Phil, I really appreciate it!

I use an Excel table named tblMyData as the data source for the chart. In the Chart, organizations H6 and H7 have their data label '*' overlapped by the custom high error bar. I'm hoping to use a version of your code to place the label above the high error bar.

The listcolumns I use for the chart are: Organization, Stratification, Plot Value, High Error Bar, and Data Label. Overall, this fits into a solution that creates ~150 charts * 20 organizations and makes a PowerPoint report for each of the organizations - this was a defect that came up in testing and I'm struggling to solve for it.


Excel 2016 (Windows) 64 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CC[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CF[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CG[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CH[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CJ[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CK[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CL[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CM[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CN[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CO[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CP[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CQ[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CR[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CS[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CU[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CV[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CW[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CY[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CZ[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DA[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DF[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DG[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DH[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DI[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DJ[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Period[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Organization[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Indicator[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Stratification[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Volume[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Observed
Rate[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Expected
Rate[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Adjusted
Rate[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Lower
95% CI[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Higher
95% CI[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Low
Error Bar[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]High
Error Bar[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Median[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]90th Percentile[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Plot value
>5[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Plot value
<=5[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Plot
value[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25th
Percentile[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]75th
Percentile[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Mean[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]MAX[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Data Label[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Num[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Max Scale[/COLOR][/TD]
[TD="bgcolor: #50A683"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Values Axis Minor Unit[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD="bgcolor: #DBEEE7"]2016/17[/TD]
[TD="bgcolor: #DBEEE7"]H1[/TD]
[TD="bgcolor: #DBEEE7"]1-Year All-Cause Mortality Following PCI[/TD]
[TD="bgcolor: #DBEEE7"]Emergent PCI[/TD]
[TD="bgcolor: #DBEEE7"]
1745​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.118995633​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.1045545​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.12​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.1028​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.1363​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0162​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0173​
[/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"]
0.12​
[/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"]
0.12​
[/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #9D9D9D"]
0.0373​
[/TD]
[TD="bgcolor: #9D9D9D"][/TD]
[TD="bgcolor: #9D9D9D"]
22​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.2​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.05​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]2016/17[/TD]
[TD]H2[/TD]
[TD]1-Year All-Cause Mortality Following PCI[/TD]
[TD]Emergent PCI[/TD]
[TD]
315​
[/TD]
[TD]
0.159722222​
[/TD]
[TD]
0.124679127​
[/TD]
[TD]
0.13​
[/TD]
[TD]
0.095​
[/TD]
[TD]
0.1741​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.039​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0401​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
0.13​
[/TD]
[TD][/TD]
[TD]
0.13​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #9D9D9D"]
0.0601​
[/TD]
[TD="bgcolor: #9D9D9D"][/TD]
[TD="bgcolor: #9D9D9D"]
22​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.2​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.05​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD="bgcolor: #DBEEE7"]2016/17[/TD]
[TD="bgcolor: #DBEEE7"]H3[/TD]
[TD="bgcolor: #DBEEE7"]1-Year All-Cause Mortality Following PCI[/TD]
[TD="bgcolor: #DBEEE7"]Emergent PCI[/TD]
[TD="bgcolor: #DBEEE7"]
506​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.103139013​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.103062158​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.11​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.0704​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.1399​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0346​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0349​
[/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"]
0.11​
[/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"]
0.11​
[/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #9D9D9D"]
0.0549​
[/TD]
[TD="bgcolor: #9D9D9D"][/TD]
[TD="bgcolor: #9D9D9D"]
22​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.2​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.05​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD]2016/17[/TD]
[TD]H4[/TD]
[TD]1-Year All-Cause Mortality Following PCI[/TD]
[TD]Emergent PCI[/TD]
[TD]
628​
[/TD]
[TD]
0.074074074​
[/TD]
[TD]
0.095905428​
[/TD]
[TD]
0.08​
[/TD]
[TD]
0.0495​
[/TD]
[TD]
0.1127​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0316​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0316​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
0.08​
[/TD]
[TD][/TD]
[TD]
0.08​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #9D9D9D"]
0.0516​
[/TD]
[TD="bgcolor: #9D9D9D"][/TD]
[TD="bgcolor: #9D9D9D"]
22​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.2​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.05​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD="bgcolor: #DBEEE7"]2016/17[/TD]
[TD="bgcolor: #DBEEE7"]H5[/TD]
[TD="bgcolor: #DBEEE7"]1-Year All-Cause Mortality Following PCI[/TD]
[TD="bgcolor: #DBEEE7"]Emergent PCI[/TD]
[TD="bgcolor: #DBEEE7"]
0​
[/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #9D9D9D"][/TD]
[TD="bgcolor: #9D9D9D"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #9D9D9D"]
0.05​
[/TD]
[TD="bgcolor: #9D9D9D"][/TD]
[TD="bgcolor: #9D9D9D"]
22​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.2​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.05​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD]2016/17[/TD]
[TD]H6[/TD]
[TD]1-Year All-Cause Mortality Following PCI[/TD]
[TD]Emergent PCI[/TD]
[TD]
357​
[/TD]
[TD]
0.14​
[/TD]
[TD]
0.106783462​
[/TD]
[TD]
0.14​
[/TD]
[TD]
0.0957​
[/TD]
[TD]
0.1797​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0413​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0427​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
0.14​
[/TD]
[TD][/TD]
[TD]
0.14​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #9D9D9D"]
0.0627​
[/TD]
[TD="bgcolor: #9D9D9D"]
*​
[/TD]
[TD="bgcolor: #9D9D9D"]
22​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.2​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.05​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD="bgcolor: #DBEEE7"]2016/17[/TD]
[TD="bgcolor: #DBEEE7"]H7[/TD]
[TD="bgcolor: #DBEEE7"]1-Year All-Cause Mortality Following PCI[/TD]
[TD="bgcolor: #DBEEE7"]Emergent PCI[/TD]
[TD="bgcolor: #DBEEE7"]
997​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.113924051​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.091444649​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.13​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.1054​
[/TD]
[TD="bgcolor: #DBEEE7"]
0.1563​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0246​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0263​
[/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"]
0.13​
[/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"]
0.13​
[/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #DBEEE7"][/TD]
[TD="bgcolor: #9D9D9D"]
0.0463​
[/TD]
[TD="bgcolor: #9D9D9D"]
*​
[/TD]
[TD="bgcolor: #9D9D9D"]
22​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.2​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.05​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD]2016/17[/TD]
[TD]H8[/TD]
[TD]1-Year All-Cause Mortality Following PCI[/TD]
[TD]Emergent PCI[/TD]
[TD]
683​
[/TD]
[TD]
0.096209913​
[/TD]
[TD]
0.119217925​
[/TD]
[TD]
0.08​
[/TD]
[TD]
0.0584​
[/TD]
[TD]
0.1111​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0264​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.0263​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
0.08​
[/TD]
[TD][/TD]
[TD]
0.08​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #9D9D9D"]
0.0463​
[/TD]
[TD="bgcolor: #9D9D9D"][/TD]
[TD="bgcolor: #9D9D9D"]
22​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.2​
[/TD]
[TD="bgcolor: #9D9D9D"]
0.05​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: PCI 1 year mortality[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I would guess that the desired graph is a column chart (similar to first chart in this thread) with the organization labels on the horizontal axis, plot values as vertical columns, low/high error bars bracketing the top of each column. I am not sure of the placement of the stratification items.

This code is a streamlined subset of the previous code based on what I understand you are asking for and the above assumptions.

Code: 
Option Explicit

Sub MoveAndEditDataLabels()

    Dim cht As Chart
    Dim ser As Series
    Dim rngSeries As Range
    Dim sFormula As String
    Dim lDLOffset As Long
    Dim rngDL As Range
    Dim lHEOffset As Long
    Dim rngHE As Range
    Dim pt As Point
    Dim sngX As Single
    Dim sngY As Single
    Dim sngColumnHeight As Single
    Dim sngScalingFactor As Single
    Dim lMaxPoint As Long
    Dim aryValues As Variant
    Dim aryDataLabels As Variant
    Dim aryErrors As Variant
    Dim sngMaxValue As Single
    Dim lPointIndex As Long
    Dim lPointCount As Long
    Dim sngValue As Single
    
    'Edit if your current setup changes
    Set cht = ActiveSheet.ChartObjects(1).Chart     'Replace 1 with your chart's index or name
    Set ser = cht.SeriesCollection("Plot Value")    'Edit if required
    lDLOffset = 5 'Relationship between CS and CX   'Edit if required
    lHEOffset = -5 'Relationship between CS and CN  'Edit if required
    
    sFormula = Split(ser.Formula, ",")(2)           'Get location of plot values
    Set rngSeries = Range(Split(sFormula, "!")(1))  'convert to range
    Set rngDL = rngSeries.Offset(0, lDLOffset)      'Get range of desired data labels
    Set rngHE = rngSeries.Offset(0, lHEOffset)      'Get range of High Error values
    
    aryValues = Range(rngSeries.Address)
    aryDataLabels = Range(rngDL.Address)
    aryErrors = Range(rngHE.Address)

    'Reset the data labels to their original position before applying correction
    If ser.HasDataLabels Then ser.HasDataLabels = False
    ser.HasDataLabels = True
    ser.DataLabels.Position = xlLabelPositionOutsideEnd
    
    'Calculate Offset Scaling Factor based on tallest column
    sngMaxValue = 0
    lPointCount = ser.Points.Count
    For lPointIndex = 1 To lPointCount
        sngValue = aryValues(lPointIndex, 1)
        If sngValue > sngMaxValue Then
            sngMaxValue = sngValue
            lMaxPoint = lPointIndex
        End If
    Next
    sngColumnHeight = ser.Points(lMaxPoint).Height
    sngScalingFactor = sngColumnHeight / sngMaxValue
    
    'Move & Edit Labels
    For lPointIndex = 1 To lPointCount
        With ser.Points(lPointIndex).DataLabel
            If Not IsEmpty(aryErrors(lPointIndex, 1)) Then
                .Top = .Top - (aryErrors(lPointIndex, 1) * sngScalingFactor) + 5
                .Text = aryDataLabels(lPointIndex, 1)
            End If
        End With
    Next

End Sub
 
Upvote 0
Thanks Phil - the desired graph is a clustered column chart. The stratification column includes the series labels. There are 3 stratification's/series: Emergent PCI, Non-Emergent PCI, and Total PCI.

Yes, the organization labels are on the horizontal axis, plot values as vertical columns, and low/high error bars bracketing the top of each column. I'm new to the board - still trying to figure out how to include a screen shot for you, in case that helps.

When I run the code, I get a parameter not valid error on this line:
Set ser = cht.SeriesCollection("Plot value") 'Edit if required
 
Upvote 0
To post an image, upload it to an image sharing site then use IMG tags around the link to include it in your post:
[IMG]https://cdn3.iconfinder.com/data/icons/developerkit/png/Ball%20Red.png[/IMG]
I used the NOPARSE tags on the above line so the BB code for posting an image would NOT be processed. The following image is referenced by using the line that you see above.
Ball%20Red.png


More image posting guidance can be found here:
https://www.mrexcel.com/forum/misc.php?do=bbcode#imgcode

Regarding:
Code: 
Set ser = cht.SeriesCollection("Plot value") 'Edit if required
I used "Plot Value" as the name of the series that held the CS column in your data, It determines the height of the columns in my version of your graph.
If your graph must have a different name for that series, so change "Plot Value" to the names of that series (keep the double quotes on each end)

I cannot get the data as presented to cluster. Are you doing some intermediate processing to get the clustered graph?
Please run this code and post the output from the immediate window in the VBE. It will help me understand how your data is laid out and how to modify the code to work with it. (Or at least allow me to ask questions from a less ignorant position).

Code: 
Sub DocumentSeries()

    Dim ser As Series
    Dim lSeriesIndex As Long
    
    'Select a chart then run this code
    If Not ActiveChart Is Nothing Then
        'A chart is selected
        Debug.Print "Start Series Documentation =============================="
        For Each ser In ActiveChart.SeriesCollection
            Debug.Print ser.PlotOrder
            Debug.Print ser.Name
            Debug.Print ser.Formula
        Next
        Debug.Print "End Series Documentation ================================"
    Else
        MsgBox "Select a chart then run this code"
    End If

End Sub
 
Upvote 0
Thanks again Phil - I'll go to the test area for the site and practice posting HTML, images, and attachments to make it easier to share information, sorry for causing any confusion.

The sample records I provided didn't include the other two data series - it only has sample records from the first stratification...I think I should have provide data that illustrated each of the three series. I think your series documentation code will help to clarify the sample.

Start Series Documentation ==============================
1
Total PCI
=SERIES("Total PCI",'PCI 30 Day Mortality'!$O$38:$O$55,'PCI 30 Day Mortality'!$AE$2:$AE$19,1)
2
Emergent PCI
=SERIES("Emergent PCI",'PCI 30 Day Mortality'!$O$38:$O$55,'PCI 30 Day Mortality'!$AE$20:$AE$37,2)
3
Non-Emergent PCI
=SERIES("Non-Emergent PCI",'PCI 30 Day Mortality'!$O$38:$O$55,'PCI 30 Day Mortality'!$AE$38:$AE$55,3)
End Series Documentation ================================


Should also note - in an effort to simplify (which I don't think I did very well) in my description and the series documentation above, I consolidated the data into one table and re-built the chart off of that. In reality, because this is part of a bigger solution which creates hundreds of charts from a larger data source, I use the advanced filter functionality to create each data series for the chart in it's own table. In a more accurate view of how it actually works, here's what the output of the series documentation based on that:

Start Series Documentation ==============================
1
Total PCI
=SERIES("Total PCI",tblMyData_TotalPCI[Organization],tblMyData_TotalPCI[Plot value],1)
2
Emergent PCI
=SERIES("Emergent PCI",tblMyData_EmergentPCI[Organization],tblMyData_EmergentPCI[Plot value],2)
3
Non-Emergent PCI
=SERIES("Non-Emergent PCI",tblMyData_NonEmergentPCI[Organization],tblMyData_NonEmergentPCI[Plot value],3)
End Series Documentation ================================

I hope that helps - thank you again!
 
Upvote 0
I modified the code to work with the series definitions in the first part of your last post.
You have to edit these lines to reflect the layout of you data
Code: 
    lDLOffset = 5 'Column relationship between Y-Values and Data Label columns
    lHEOffset = -5 'Column relationship between Y-Values and High Error


Code: 
Sub EditAndMoveDataLabel()

    Dim cht As Chart
    Dim ser As Series
    Dim rngSeries As Range
    Dim sFormula As String
    Dim rngDL As Range
    Dim rngHE As Range
    Dim pt As Point
    Dim sngX As Single
    Dim sngY As Single
    Dim sngColumnHeight As Single
    Dim sngScalingFactor As Single
    Dim lMaxPoint As Long
    Dim aryValues As Variant
    Dim aryDataLabels As Variant
    Dim aryErrors As Variant
    Dim sngMaxValue As Single
    Dim lPointIndex As Long
    Dim lPointCount As Long
    Dim sngValue As Single
    Dim lSerIndex As Long
    Dim lDLOffset As Long   'The relative position of the Data Label column from the Y-Values column
    Dim lHEOffset As Long   'The relative position of the High Error column from the Y-Values column
                            '-1 would be one column to the left, 4 would be four columns to the right
    
    'For the original sample data, CS held the Y-Values, CX held the Data labels and
    '  CN held the High Error values.  So the relationship between the columns was:
    lDLOffset = 5 'Column relationship between Y-Values and Data Label columns
    lHEOffset = -5 'Column relationship between Y-Values and High Error
    'Edit the above numbers to match the current column relationships
    
    'Extra steps are required if the series formulas use named range or table references.
        
    Set cht = ActiveSheet.ChartObjects(1).Chart     'If more than one chart on the activesheet
                                                    '  replace 1 with your chart's index or name
    
    For lSerIndex = 1 To cht.SeriesCollection.Count
    
        Set ser = cht.SeriesCollection(lSerIndex)
        
        'Split function creates a 0..n array of the text value in the first argument divided by the
        '  single text character in the second argument
        sFormula = Split(ser.Formula, ",")(2)           'Get location of plot values as text
        
        'Sample sFormula layout example for column chart series:
        '=SERIES("Plot Value",Sheet1!$CD$3:$CF$40,Sheet1!$CS$3:$CS$40,1)            'SERIES Formula
        '         Name       ,X-Axis (Category)  ,Y-Axis (Value)     ,Plot Order    'Formula Part Names
        '         0           1                   2                   3             'Split Array Index
        
        Set rngSeries = Range(Split(sFormula, "!")(1))  'Get range of Y-Axis cells
        Set rngDL = rngSeries.Offset(0, lDLOffset)      'Get range of desired Data Labels
        Set rngHE = rngSeries.Offset(0, lHEOffset)      'Get range of High Error values
        
        aryValues = Range(rngSeries.Address)            'Get values for each point
        aryDataLabels = Range(rngDL.Address)            'Get desired Data Labels for each point
        aryErrors = Range(rngHE.Address)                'Get high error values for each point
    
        'Reset the data labels to their original position before applying correction
        If ser.HasDataLabels Then ser.HasDataLabels = False
        ser.HasDataLabels = True
        ser.DataLabels.Position = xlLabelPositionOutsideEnd
        
        'Calculate Offset Scaling Factor based on tallest column
        sngMaxValue = 0
        lPointCount = ser.Points.Count
        For lPointIndex = 1 To lPointCount
            sngValue = aryValues(lPointIndex, 1)
            If sngValue > sngMaxValue Then
                sngMaxValue = sngValue
                lMaxPoint = lPointIndex
            End If
        Next
        sngColumnHeight = ser.Points(lMaxPoint).Height
        sngScalingFactor = sngColumnHeight / sngMaxValue
        
        'Move & Edit Labels
        For lPointIndex = 1 To lPointCount
            With ser.Points(lPointIndex).DataLabel
                If Not IsEmpty(aryErrors(lPointIndex, 1)) Then
                    .Top = .Top - (aryErrors(lPointIndex, 1) * sngScalingFactor - 3)
                    .Text = aryDataLabels(lPointIndex, 1)
                End If
            End With
        Next
        
    Next

End Sub

Which of the layouts in your last post should be used to construct the code to move the data labels?
For each series involved, the code has to "know" the location of 3 ranges:
1) range that contains the vertical value of the column
2) range that contains the positive errors
3) range that contains the new data labels

If any range specification uses named ranged or table references then the above code will required some changes. Let me know if that is the case or if the above code does not work as desired.

This was the series documentation for the graph I tested:

Start Series Documentation ==============================
1
A
=SERIES("A",Sheet1!$CF$17:$CF$23,Sheet1!$CS$3:$CS$9,1)
2
B
=SERIES("B",Sheet1!$CF$17:$CF$23,Sheet1!$CS$10:$CS$16,2)
3
C
=SERIES("C",Sheet1!$CF$17:$CF$23,Sheet1!$CS$17:$CS$23,3)
End Series Documentation ================================
 
Upvote 0
This is amazing Phil - for the series documentation you tested, this works like a charm. The real structure of the data as you noted is the second version, where each of the three data series are in three different data tables...it follows this series documentation:

Start Series Documentation ==============================
1
Total PCI
=SERIES("Total PCI",tblMyData_TotalPCI[Organization],tblMyData_TotalPCI[Plot value],1)
2
Emergent PCI
=SERIES("Emergent PCI",tblMyData_EmergentPCI[Organization],tblMyData_EmergentPCI[Plot value],2)
3
Non-Emergent PCI
=SERIES("Non-Emergent PCI",tblMyData_NonEmergentPCI[Organization],tblMyData_NonEmergentPCI[Plot value],3)
End Series Documentation ================================

In my code I reference the three tables using : .ListObjects("tblMyData_TotalPCI"), .ListObjects("tblMyData_EmergentPCI"), and .ListObjects("tblMyData_NonEmergentPCI")
and I reference the columns using : .ListColumns("Organization").DataBodyRange, .ListColumns("Plot value").DataBodyRange, .ListColumns("High Error Bar").DataBodyRange, and .ListColumns("Data Label").DataBodyRange

I hope that makes sense - let me know if any questions - thanks so much!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
S
  • Question Question
Adding two data labels to a bar chart
Replies
0
Views
760
Sundance_Kid
S
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
R
  • Solved
How do I extract the data label of a trendline in VBA?
2
Replies
14
Views
2K
RockandGrohl
R
H
  • Solved
Catching the Mouse Click Error in Modal User Form
Replies
4
Views
760
huiettcm
H

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top