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.

Customer Question

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
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  Rachel-Mod replied 2 years ago.

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!

Expert:  Val B replied 2 years ago.

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 2 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 2 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


 

Expert:  Val B replied 2 years ago.

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 2 years ago.

thanks!

Expert:  Val B replied 2 years ago.

Break time!

Instructions and changes made on the attached.

ValExcel

Customer: replied 2 years ago.

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

Customer: replied 2 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.

Expert:  Val B replied 2 years ago.
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 4 other Microsoft Office Specialists are ready to help you
Customer: replied 2 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

Expert:  Val B replied 2 years ago.

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 2 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'

Expert:  Val B replied 2 years ago.

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 2 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


 


 

Expert:  Val B replied 2 years ago.

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.

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
Val B
Val B
Senior Analyst/Trainer
31 Satisfied Customers
Expert in solving Microsoft Office problems.