This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.
Solved
Complex Excel Graphic Assistance
Posted on 2011-03-07
I have a Excel Graphic that was done by an outstanding EE some time ago for me. I now have an immediate need to modify it but do not have the skills to do so. I have attached my attempt at modifying the graphic (XLSM) and a copy of the desired graphical result (embedded in the spreadsheet as a picture)..
In Summary;
It's a 12 category (y-axis) across a 5 category (x-axis) bar chart with arrow
Three levels of solid fill
Two data points at the top of the first two solid fills
And an Arrow that goes from the first point to the second point.
Much thanks in advance,
Bright01
Complex-Chart-Modificationv1.xlsm
In Summary;
It's a 12 category (y-axis) across a 5 category (x-axis) bar chart with arrow
Three levels of solid fill
Two data points at the top of the first two solid fills
And an Arrow that goes from the first point to the second point.
Much thanks in advance,
Bright01
Complex-Chart-Modificationv1.xlsm
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
2
9 Comments
it is complex. I am getting closer but still need a few hours to work on the graph.
complex.jpg
complex.jpg
Active today
Royhsiao,
This looks like you've got the basics down. I can change the colors and with your help, use math to plot the graph correctly. Appreciate the help.
B1
This looks like you've got the basics down. I can change the colors and with your help, use math to plot the graph correctly. Appreciate the help.
B1
Hello Bright,
I see you've had some good help already. Seeing that I'm already familiar with the chart, please have a look to see if the attached is what you are after.
I've added a calculation in column G for the Gap arrows error bars, so they extend back to the orange dot. The values in this column are used in the Error bars for the "Series GAP X error bars" under "Custom > Specify Value > Negative error value"
I've added a data series (stacked column) to make the Gap a green bar. The values for that are in column H. It's actually a copy of column G data, but may be easier to understand if these values live in different cells.
I've added a data series (stacked column) to make the remainder a blue bar. The source for this is in column J. It's a simple subtraction of Current State + Gap from the max value of 5.
Is that what you had in mind?
cheers, teylyn
Copy-of-Complex-Chart-Modificati.xlsm
I see you've had some good help already. Seeing that I'm already familiar with the chart, please have a look to see if the attached is what you are after.
I've added a calculation in column G for the Gap arrows error bars, so they extend back to the orange dot. The values in this column are used in the Error bars for the "Series GAP X error bars" under "Custom > Specify Value > Negative error value"
I've added a data series (stacked column) to make the Gap a green bar. The values for that are in column H. It's actually a copy of column G data, but may be easier to understand if these values live in different cells.
I've added a data series (stacked column) to make the remainder a blue bar. The source for this is in column J. It's a simple subtraction of Current State + Gap from the max value of 5.
Is that what you had in mind?
cheers, teylyn
Copy-of-Complex-Chart-Modificati.xlsm
well I am done...finally...
Complex-Chart-Modificationv1.xlsm
Complex-Chart-Modificationv1.xlsm
Good Job teylyn. Nice description.
I add 2 more columns next to the current state.
The total length of the bar is 7 if you sum them up.
I update the GAP arrows so it will be the difference of assess current state and future target level.
I hope this help.
I add 2 more columns next to the current state.
The total length of the bar is 7 if you sum them up.
I update the GAP arrows so it will be the difference of assess current state and future target level.
I hope this help.
Active today
Teylyn,
This looks like what I need...give me a day or two to digest it. In the meantime, I'm very glad to hear you were not part of the ChristChurch situation. What a terrible event. I hope there is a way for you through EE to let people know, who are affiliated with you, that you need assistance in the event that you are in such a situation.
Again, thank you and Royhsiao for this effort on the graphics....I'll be back in touch shortly.
B.
This looks like what I need...give me a day or two to digest it. In the meantime, I'm very glad to hear you were not part of the ChristChurch situation. What a terrible event. I hope there is a way for you through EE to let people know, who are affiliated with you, that you need assistance in the event that you are in such a situation.
Again, thank you and Royhsiao for this effort on the graphics....I'll be back in touch shortly.
B.
Active today
Teylyn,
Can you take one more quick pass at this? It's 90% of what I'm looking for. In trying to clean it up and fit it to the workbook, it seems that it may be easier to add the top headings in cells vs. textboxes. The last two plot columns can also be removed since I'll again use cells next to the graph (aligned with the rows) to add the last two columns (Level of effort, Level of return). My thinking on this is that I will be using variables for the title headings and it's a little difficult in lining up the boxes; I would think that having cells would be easier.
I hope all is well with you,
Best regards,
Bright01
Can you take one more quick pass at this? It's 90% of what I'm looking for. In trying to clean it up and fit it to the workbook, it seems that it may be easier to add the top headings in cells vs. textboxes. The last two plot columns can also be removed since I'll again use cells next to the graph (aligned with the rows) to add the last two columns (Level of effort, Level of return). My thinking on this is that I will be using variables for the title headings and it's a little difficult in lining up the boxes; I would think that having cells would be easier.
I hope all is well with you,
Best regards,
Bright01
Hello Bright,
I'm not so sure that aligning the chart with underlying columns is such a good idea. The Y axis labels will take up a varying amount of space, depending on how wide your longest title string is, and you'll be forever adjusting the columns underlying the charts.
If your title texts remain the same, though, you could try this:
Format the Chart Area to be transparent (no fill)
format the Plot area with white fill
remove the text boxes
set the primary and secondary X axes to a maximum of 5 (instead of 7)
resize the plot area horizontally to the right, so it is closer to the right hand side of the Chart Area, but leaving enough room for a full data marker.
Align the column widths behind the chart to match with the X axis major units.
Use the cells behind the chart to enter the text that used to be in the text boxes, format to suit.
I faintly recall that there was a macro that resized the plot area according to the number of line items in the chart. That macro made sure a certain space at the top of the chart, above the plot area was kept free to accommodate the text boxes. This may need to be revisited, and changed to let the chart defaults kick in. That way, the chart won't have to overlay the blue cells with white text.
The blue cells are a bit awkward to access at the moment, since you can't select them with the mouse. You need to select a cell outside of the chart area and then use the arrow keys to navigate to the blue cells. Fixing the macro mentioned above will get rid of the white space in the chart area, and the chart can be placed underneath the blue cells instead of overlaying them.
Does that make sense?
I can't make any structural changes to the sheet, since every time I even change a number, the change event kicks in and refers to a sheet that's not included in the sample file, and then bombs.
Ideally, I'd have moved the chart to an empty area of the sheet, where the column resizing does not affect any data.
I hope you get the idea of what I'm trying to do in the attached.
cheers, teylyn
Copy-of-Complex-Chart-Modificati.xlsm
I'm not so sure that aligning the chart with underlying columns is such a good idea. The Y axis labels will take up a varying amount of space, depending on how wide your longest title string is, and you'll be forever adjusting the columns underlying the charts.
If your title texts remain the same, though, you could try this:
Format the Chart Area to be transparent (no fill)
format the Plot area with white fill
remove the text boxes
set the primary and secondary X axes to a maximum of 5 (instead of 7)
resize the plot area horizontally to the right, so it is closer to the right hand side of the Chart Area, but leaving enough room for a full data marker.
Align the column widths behind the chart to match with the X axis major units.
Use the cells behind the chart to enter the text that used to be in the text boxes, format to suit.
I faintly recall that there was a macro that resized the plot area according to the number of line items in the chart. That macro made sure a certain space at the top of the chart, above the plot area was kept free to accommodate the text boxes. This may need to be revisited, and changed to let the chart defaults kick in. That way, the chart won't have to overlay the blue cells with white text.
The blue cells are a bit awkward to access at the moment, since you can't select them with the mouse. You need to select a cell outside of the chart area and then use the arrow keys to navigate to the blue cells. Fixing the macro mentioned above will get rid of the white space in the chart area, and the chart can be placed underneath the blue cells instead of overlaying them.
Does that make sense?
I can't make any structural changes to the sheet, since every time I even change a number, the change event kicks in and refers to a sheet that's not included in the sample file, and then bombs.
Ideally, I'd have moved the chart to an empty area of the sheet, where the column resizing does not affect any data.
I hope you get the idea of what I'm trying to do in the attached.
cheers, teylyn
Copy-of-Complex-Chart-Modificati.xlsm
Featured Post
Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Course of the Month7 days, 22 hours left to enroll
610 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.