Sliding scale graph or graph with only X axis?

Hello all,

I have a cell calculating the average of a range of cells which results in a value between -2 and +2 that I want to visually represent as in the picture below but cannot, for the life of me, figure out how to do so. Help?

Sliding Scale.png

As an adjunct question (or maybe a concession which would also be helpful), can anyone explain to me how to use a formula to convert this number to a percentage of the same range? Ie., -2 = 0% and +2 = 100%, so a score of -0.113 would equal 47.2%.

Many thanks for your expertise.

Originally Posted by MrShorty

It seems like it should be as easy as creating an XY scatter chart -- but deleting/hiding/removing the Y axis. Here's how I created this:

1) In A1 enter my number that ranges from -2 to 2. In B1 I enter 0.
2) Insert scatter chart and fix up the data series definitions (I usually use the select data dialog) so that B1 is the Y values for the only data series and A1 is the X values.
3) Delete or hide the y axis.
4) Format the X axis to have a thick line with a gradient color scheme and with fixed min at -2 and fixed max at +2.
5) Format the data series to have a suitable marker.

That should be close to what you want.

Incredibly helpful, thank you!

Originally Posted by MrShorty

As for the other question -- isn't that simple linear interpolation? (y-0)/(-0.113-(-2))=(1-0)/(2-(-2)) solve for y?

This one was less clear to me. Can you tell me exactly what I should enter into the formula bar? Entering the formula as you wrote it =(1-0)/(A1-(-2)) where A1 is my -0.113 cell resulted in 0.3636 which is not the correct answer. Forgive me, I'm better than you average bear at Excel, but math on the other hand...

Originally Posted by MrShorty

Solving the linear interpolation equation is a common application of solving proportions or rational equations that you would have learned back in early secondary algebra. I believe there is value in reviewing those math principles you should already know:

(y-0)/(-0.113-(-2))=(1-0)/(2-(-2)) multiply by (-0.113-(-2)) to get y alone
y=1*(-0.113-(-2))/(2-(-2)) -- and there you go. If you have 1 in A1, -0.113 in say A2, -2 in A3, and 2 in A4, then your linear interpolation formula become =A1*(A2-A3)/(A4-A3)

Well the little lecture about how I should spend my time or what I should know was completely useless but the rest was helpful, thanks.