Hi Matthew

Can you post some sample data and your expected result (with some explanation) for us to understand your needs better.

Sunny

Hi Matthew

I don't think that an area chart is suitable for what you wanted to display.

Anyhow you can give this a try.

Sunny

Just to show you how odd it would look with an area chart, here is another try.

I overlay one chart on top of another to create the effect.

Sunny

Hi Sunny

Thanks for the help!  I actually do not think I was very clear about my example-

Heres an example of what I am looking at;

The scale on the area chart is actually set to zero point. so an employee would either be over or under expectation, with meeting charted at zero.

For example

John  Week 1       +5.0

John  Week 2        +1.2

John  Week 3        -2.5

John  Week 4        -3.0

John  Week 5        -4.5

John  Week 6        +4.2

If that makes sense?

Hi Matthew

Sorry if I am not clear about your needs.

It would be better if you could attach an image of how you wanted the chart to look like (with the above data).  Even a hand-drawn image would suffice.

In the mean time see if the attached meet your needs.

What I did was copy the colored image and paste it onto the area chart.

You will then need to adjust the charts Data Series - Fill - Picture Or Texture Fill - Offsets Top/Bottom to adjust the colors position.

This adjustment is not dynamic so you will need to do it for every chart.

If if is not good enough then you can refer here  https://peltiertech.com/area-chart-invert-if-negative/

Sunny

Here is another try.

1) Create 2 similar area charts with different color (red and green)

2) Fix the axis max and min value (to prevent the chart from resizing)

3) Copy and paste red chart (you will need to select the range behind the chart to copy) as linked image and crop the top positive values image.

4) Move the cropped chart and superimpose onto the green chart.

Sunny

A 3rd method, copy the chart and paste link image.

Now set the chart fill color to transparent (PictureTools-Format-Color-Set Transparent Color)

Now the chart area is transparent and you can superimpose over cells filled with red and green colors.

Again, you must fix the axis max and min values to prevent resizing.

Sunny

can you link conditional formatting to an area chart,, so that it changes colour according to the values of several cells behind the chart area and is dynamic?

eg

if you had 4 KPIs,, if all are red, then overall KPI chart area shows red but if all are green, then overall KPI is gold and if a mixture of colours in the 4 basic KPis, then overall KPI shows orange...,, And this formatting of the overall KPI is dynamic so will change as the 4 basic KPIs change

i can easily do conditional formatting on the basic KPIs but cannot fathom how to do the overall one.

any ideas? Would an array function help me?

Thanks for you ideas in advance

DH

Hi DH

Since you are able to conditional format the cells, I am sure there must be some values the cells are referring to.

There is no need to refer to the color of the cells to CF the chart. Just refer to the CF values instead.

As I don't know what these values are, I will just assume they are Red and Green.

Based on these values, I have created a sample (refer attachment). The test data generates some random numbers when you press F9.

No idea what your gold color looks like but you can play with the fill color using the RGB.

If this is not what you are looking for then can you post a sample of the KPI and the expected result?

It will help us to understand your needs better.

Sunny

Hi Sunny

I  can't attach a file for some reason (!) i don't think i explained myself clearly enough:

but here is the essence of the query :

the individual KPIs are set to change colour in a sheet depending on their values as below 

the Overall KPI box needs to turn Red if any of the Individual KPIs are red, Gold if the individuals are all green, Green if there are 4 green and 2 amber  and amber if there is a mixture of amber and green, 

we can do this by assigning values of 1 in a separate chart and then counting the outcome values , but i would love to know if it is possible to do it in one calculation in the KPI outcome cell.

i can get it to turn gold if all green conditions are met and then another colour if not or red if one KPI is red but I can't get a formula that will account for all 4 overall outcomes in one formula..

any ideas ?

This is the best I could come up with. It uses a helper column A.

The CF counts the colors and determine if it meets a specific combination to format the KPI box and Chart.

Please note that the KPI box and Chart default color is Amber (Yellow) so you don't need to check for miscellaneous color combinations other than those that you had already defined.

Good luck.

Sunny

thank you so much ,

this will really help

DH