Formulas to manipulate a chart

[CraigsWorld]

Hi there...

I am trying to create a formula that will move the pointer (pie chart) over a doughnut (speedometer) chart to fit my needs. Green will be all numbers 14 and under, yellow is greater than 14 and less or equal to 35, red is greater than 35... this will never change. What will change is the size (values) of the green, yellow and red data point. In Test A image below the 405 value is all cells with values that are 14 or less (green), the 119 are cell values that are over 14 and less than or equal to 35 (yellow) and the 117 are cell values greater than 35 (red).

In the Test A image below, you can see that the value 14 pointer is on the end of the green. In order to do this I had to manipulate the end value in S4 (set at 31).

In the Test B image, I switched the values of green and yellow to simulate a change, in order to keep the pointer within the green... I had to change the value of cell S4 to 140.

What I am trying to do is create a formula using the P column values to set S4 accordingly. So 0 or less would always be at the start of the green and 14 would be at the end of the green, numbers in-between would be displayed at various points in the green.

15 would show at the start of yellow and 35 would display at the end of yellow... same for red, 36 would be the start of red... and I would use a 200 cutoff (always) to display at the end of red.

Would a formula like this be possible? I have no idea how to calculate this, any help to get me in the right directions would be greatly appreciated.

I hope I explained this correctly...

Thanks Craig

Test A Image
TestA.jpg




Test B Image
TestB.jpg

[avk]

Refer attach file. Hope this help you.