How do i get this chart effect

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: How do i get this chart effect

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Posts
    1,068
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How do i get this chart effect

     
    Hi

    I have some data which gives me ydays score and todays score

    I want o show any figure that is greater than 3 in red else show it as green

    So for each team i would need to 2 plots ydays score and todays score
    Again depending on what the score was it will need to change colour
    So side by side for each team i can see whether its in the Green or Red comparing both days

    Here was my attempt

    Yday Red Yday Green Today Red Today Green
    TEAM A 2.25 3.08
    TEAM B 2.08 3.92
    TEAM C 1.93 2.57

    The formulas I used

    =IF(D2>3,D2,"") - Copied down for yday red
    =IF(D2<3,D2,"") - copied down for yday green
    =IF(E2>3,E2,"") - copied down for today red
    =IF(E2<3,E2,"") - copied down for today green

    The formulas is there to show the values but struggling to get the Chart to actually show it side by side for both days to see what colour it was for yday and today

    Hope that helps

    Thanks

  2. #2
    Board Regular
    Join Date
    Nov 2011
    Posts
    1,068
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do i get this chart effect

    Bump

  3. #3
    Board Regular
    Join Date
    Nov 2011
    Posts
    1,068
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do i get this chart effect

    Hi

    Any luck tips please?

  4. #4
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,400
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do i get this chart effect

    Your formulas exclude the case where the score is exactly 3. Green occurs when the value is <3, red when it is >3. You should either make green <=3 or red >=3, or include a third condition for =3.

    I set up my formulas as shown below, selected the shaded two-area range, and made a clustered column chart. That didn't work, so I made a stacked column chart. That also didn't work.



    What you need is a stacked and clustered column chart. This isn't built into Excel, but you can easily enough fake it.

    I've staggered my data as shown below, where yesterday's and today's data are on different rows, and a blank row appears between categories (teams). From the shaded data I made a stacked column chart, and set the series gap width to zero. You don't see anything stacked, because one item of each stacked pair is always zero, but believe me, yesterday green is stacked on yesterday red, and the same for today green and red.

    I put dots in the first column to help Excel plot the data correctly. The chart below is how it looks with the dots cleared after the plot is created.



    This still isn't perfect. The team names aren't centered under the clusters of columns. You can read my tutorial about Clustered and Stacked Column and Bar Charts to see how to make the chart above look better (below). Alternatively I've written Excel VBA software that makes this type of chart easily.

    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  5. #5
    Board Regular
    Join Date
    Nov 2011
    Posts
    1,068
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do i get this chart effect

    Hi

    Firstly i want to say thank you so much

    yes I completely missed the = 3 condition so i will need to add that and make that orange so yday orange and today orange

    if i was to that - would the layout be totally different to the way you did it above?

    Yes you are very right that the names are not centred..i could see the vba part or anything to centre it..Would i need some code to centre the names or would i need to fornat it differently to achieve this look?

    Again thank you

  6. #6
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,400
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do i get this chart effect

    I did my charts using the appropriate formulas, so the layout is correct.

    To see how to get centered labels, read the tutorial I cited in my earlier post.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  7. #7
    Board Regular
    Join Date
    Nov 2011
    Posts
    1,068
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do i get this chart effect

      
    Hi Jon..any chance you can have a look at this thread for me..its based around this thread you helped me with but converting it to pivot chart

    thanks

    http://www.mrexcel.com/forum/excel-q...ormatting.html

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com