CODEWITHSUNDEEP
excelmathgraphexcel-formula
Brad
Brad

Reputation: 1480

Altering values as a percentage to work with a graph

I have a gauge graph that goes from 0 to 100. I have divided out my justification points as how they would show on the 0 - 100 graph. -2STDev, -1STDev, Avg, +1STDev. +2 STDev. How would I go about transferring incoming values to a 0 - 100 scale to match the graph?

On the graph of 0 - 100: 
16 represents -2STDev
33 represents -1STDev
50 represents Average
66 represents +1 STDEV
83 represents +2 STDEV

My current values that I want to format to a scale of 100 to fit the graph are:

-2STDev = 63.9
-1STDev = 66.8
AVG = 69.6
+1STDev = 72.5
+2STDev = 75.4

How would I go about creating a formula to adjust these to a 0 - 100 scale? Of course my incoming value, will have to also follow this formula to be graphed upon these.

Upvotes: 0

Views: 51

Answers (1)

Kaiser
Kaiser

Reputation: 2005

You can use the following long formula:

=IF(E6<C1,E6/C1*B1,IF(E6<C2,(E6-C1)/(C2-C1)*(B2-B1)+B1,IF(E6<C3,(E6-C2)/(C3-C2)*(B3-B2)+B2,IF(E6<C4,(E6-C3)/(C4-C3)*(B4-B3)+B3,IF(E6<C5,(E6-C4)/(C5-C4)*(B5-B4)+B4,(E6-C5)/(100-C5)*(100-B5)+B5)))))

See the location of the data so you can replace for other if required.

enter image description here

Keep in mind this formula does a linear conversion for values inbetween each one of the values you have.

Upvotes: 1

Related Questions