How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Val B Your Own Question
Val B
Val B, Analyst / Trainer
Category: Microsoft Office
Satisfied Customers: 54
Experience:  Expert in solving Microsoft Office problems.
36150026
Type Your Microsoft Office Question Here...
Val B is online now
A new question is answered every 9 seconds

I attached a PPT that has a double chart embedded in it.

This answer was rated:

I attached a PPT that has a double chart embedded in it.



each of the two charts has 4 data points (Vehicle, Fuel, battery, other) and a total (TCO, which could just be the 5th data point). Notice 'Other' can be negative.



I would like to (within excel) to automatically produce this graph using the 8 (or 10) data points. with nearly this exact formatting.



http://wikisend.com/download/150608/justanswer.autograph.pptx

Hello,

I'm a moderator for this topic. I've been working hard to find a Professional to assist you with your question , but sometimes finding the right Professional can take a little longer than expected.

I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.

Thank you!

The graphs are on the attached Excel spreadsheet. Make sure both graphs are selected (Shift + Click on each) before printing and they will both print on the same page. The data for both graphs is on the Data tab.

Let me know if you have any questions.

Val

Attachment: 2012-11-02_044342_book1.xlsx

Customer: replied 4 years ago.

Wow Val this is great. I need just a bit more


 


1. both charts to be the same scale (because we are comparing side to side) two solutions, the scale can be auto scale based on the higher of the two numbers


 


2. I grasp the 'low to high' solution you inputted. I could probably adapt to that from the 4 data points needed to get the graph going. but how would I , automatically input the 'straight connectors' between the columns


 


3. in Electric vehicle chart, 'other' could be negative (as it is now) but could be positive as well. How do I handle that automatically including changing the color (red negative, same blue if positive)


 

Customer: replied 4 years ago.

Wow Val this is great. I need just a bit more


 


1. both charts to be the same scale (because we are comparing side to side) two solutions, the scale can be auto scale based on the higher of the two numbers


 


2. I grasp the 'low to high' solution you inputted. I could probably adapt to that from the 4 data points needed to get the graph going. but how would I , automatically input the 'straight connectors' between the columns


 


3. in Electric vehicle chart, 'other' could be negative (as it is now) but could be positive as well. How do I handle that automatically including changing the color (red negative, same blue if positive)


 


just a few more thoughts, since the Y axis has to be the same, is there a way to set a 'fixed axis' via a parameter? Then we do an if statement on the larger of the two totals (in current case 65,141 and 51,200) add a little bit and round up to nearest 5000 and that would be the axis (to both charts)


 


lastly reminder that the chart has to change automatically based on the parameters


 

Hi,

I'm at my full-time job today but I will get back to you later this afternoon with changes and some instructions.

Val

Customer: replied 4 years ago.

thanks!

Break time!

Instructions and changes made on the attached.

ValExcel

Customer: replied 4 years ago.

it seems the attachment is not working correctly can you resend please

Customer: replied 4 years ago.

Hi Val,


 


This is a sales tool where we will input data from a customer and present to them two side by side solutions (electric vs diesel in this case) so the outcome has to be automatic. The source in the PPT was done manually but that is the point to change that to automatic.


 


1. I changed (as example) C6 to 9000 and the scale of the left graph went to 80000 right graph stayed the same at 70000


 


2. We need to be creative and find a way to change the placement of the line connectors (while this seems minor without it we are back to square one, a manual solution)


 


3. I may be able to live with the negative in red manually, lets leave that to later


 


3.

I added four additional data series to draw the lines.

Attachment: 2012-11-02_210251_doublegraph.xlsx

Val B, Analyst / Trainer
Category: Microsoft Office
Satisfied Customers: 54
Experience: Expert in solving Microsoft Office problems.
Val B and other Microsoft Office Specialists are ready to help you
Customer: replied 4 years ago.

Thanks, XXXXX XXXXX great and I will work on it.


 


Is there any coding (VBA/Macros?) or is it all about chart configuration


 


the lines I would like to shorten them a bit (so they reach right corner to left corner not middle to middle of the adjacent columns)


 


How did you code a single (negative) column red?


 


Rating you top notch

Thanks!

I didn't use any VBA or macros; you are right it's all about configuring the chart.

For the lines you could try a different dash type by right-clicking on that data point, Format Data Series, Line Style, Dash Type

To make a data point red, right-click on it and choose Format Data Series, Fill, Solid Fill, change the dropdown color to Red.

Customer: replied 4 years ago.

Some more very minor points about this graph.


 


1. On the right graph no matter what I do I cannot change the label of the TCO. It is stuck at £52,000


 


2. I know understand how you did the lines. you made 4 new series, each has 5 data points (the same) connected them with lines. and made the line color 'no-line) for the data points except the one we wanted highlighted. I am not clear how you defined some of the lines 'no-line' and some 'seen-line'

The problem with the right graph is the negative value for "Other". If you actually enter that number as negative in cell M6 and change M7 to =Sum(M3:M6) the TCO will be computed correctly but the Other value will show up on the graph at the bottom. It's not logical to have the negative value show near the top of a graph so I did a workaround by making the summing the TCO manually instead of with a formula.

I added another data series "Real Values". When you change data, enter it into this column. There is a formula in "Other"-"High" that will change this to the absolute value (positive value) regardless if there is a negative value entered in "Real Values"

Attachment: 2012-11-04_160412_doublegraph.xlsx

On the lines, I manually made the lines formatted as "no-line" where applicable. The formulas in the "Line" data series are set to automatically change where the lines are drawn, and the "no-line", "seen-line" should stay the same unless there is something odd about the data, in which case the formatting on the lines may need to be changed.

Customer: replied 4 years ago.

I am attaching a link of tweaks I made to the previous version you sent.


 


I also added a separate 'originating data' series to correctly calculate TCO


 


I used IF statements to set different data is the 'other' is positive or negative.


 


I still cannot change the label of TCO of the right graph, it is stuck on £52,500 no matter what.


 


The version you just sent me also has this problem TCO stuck at £52,500 (also I changed 'other' to a positive figure) it did not chart well, have a try


 


I still don't understand how you actually format "no-line", "seen-line". Let's take 'line 2' as an example, it is 5 data points, so 4 'in between' lines. We want line 1,3,4 as 'no-line' and line 2 as 'seen-line' how do you get access to each of these lines to tweak the formatting (I want to do it my self but cant figure it out)


 


http://wikisend.com/download/430022/2012-11-02_210251_doublegraph-AB.xlsx


 


 

Try right-clicking on the 52,500 label and click Reset Label, that worked on mine.

To pick the different data points click on any of the data series then use the up and down arrow keys to move to the line you want to change. Then single right-click on the individual data point to change the formatting of the line. The single right-click takes some practice to master.