RAG status chart - help with weightings

[CraigG]

Hi,

I have created a dial chart (see image) for red, amber, green status. Sorry I can't attached the Excel file or use the link due to security settings.

The pointer in the chart is based on the value in H2 - in the example in image this is 50.
In cells K2, K3 and K4 it counts number of instances in column B

I need a way to weight the red, amber, green to calculate the value in cell H2.
NB - the current value in H2 (which is 50) is NOT currently linked, calculated from anything, I've just used it to show how the pointer works.

I can't work out how I would calculate the 'value' (H2) to get the pointer to be in the right position.
In the example, I have 1 x Red and 2 x Amber but no Green.
In theory this is:
Red 33%
Amber 66%
Green 0%
But what value would I use in H2?
If I use 66 the pointer would be too much in the green.

I'm not sure if by using % is the right way to go. It's difficult to explain what is needed.
The pointer would be at the mid-way point with the value 50, fully at green with 100 and fully at red with 0 but I'm lost how I would get a value based on counts from column B or column K!

Can anyone help?

 

[myall_blues]

I'm not sure if this helps, but rather than try to deal with what three colours mean I'd suggest treating them in terms of their 'greenness'. In the spreadsheet shown below, in A8:B10, I have set Red as having zero green, amber has 50% green and green by definition is 100% green.
Then in column L I multiplied the 'greenness' of each colour by the count value in column K. If I then add up all those weights and divide by 3, which is the number of KPIs, I get a value in H2.
If you test it by changing the colours against the KPIs in B2:B4 you will find that the value in H2 changes accordingly.

Hope this helps.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	KPI	Status					Pie			Data		Green weight
2	KPI1	Amber					Value	33.3		Red	1	0
3	KPI2	Red								Amber	2	100
4	KPI3	Amber								Green	0	0
5
6
7	"Greenness" weighting
8	Colour	Weight
9	Red	0
10	Amber	50
11	Green	100
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUM(L2:L4/3)
K2:K4	K2	=COUNTIFS($B$2:$B$4,J2)
L2:L4	L2	=K2*VLOOKUP(J2,A9:B11,2,FALSE)

 

[CraigG]

I'm not sure if this helps, but rather than try to deal with what three colours mean I'd suggest treating them in terms of their 'greenness'. In the spreadsheet shown below, in A8:B10, I have set Red as having zero green, amber has 50% green and green by definition is 100% green.
Then in column L I multiplied the 'greenness' of each colour by the count value in column K. If I then add up all those weights and divide by 3, which is the number of KPIs, I get a value in H2.
If you test it by changing the colours against the KPIs in B2:B4 you will find that the value in H2 changes accordingly.

Hope this helps.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	KPI	Status					Pie			Data		Green weight
2	KPI1	Amber					Value	33.3		Red	1	0
3	KPI2	Red								Amber	2	100
4	KPI3	Amber								Green	0	0
5
6
7	"Greenness" weighting
8	Colour	Weight
9	Red	0
10	Amber	50
11	Green	100
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUM(L2:L4/3)
K2:K4	K2	=COUNTIFS($B$2:$B$4,J2)
L2:L4	L2	=K2*VLOOKUP(J2,A9:B11,2,FALSE)

Thank you so much! This is perfect!
Thanks again!