Thanks:  0
Likes:  0

# Thread: How do i get this chart effect

1. ## 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

Bump

Hi

4. ## 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.

5. ## 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. ## 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.

7. ## 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

#### Posting Permissions

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