I recieved an email from Hari, who writes:
"I saw your post about smart scrollbars…how do I make scrollbars accept percentages?"
First of all Hari, may I say you have great taste when it comes to the Blogs you read. Now to answer your question:
Unfortunately, Scroll Bars in Excel only accept integers. That is to say, it can't pass values like .82. The trick is to use a simple formula to convert the whole number outputs from the Scroll Bar.
.
Step 1:
Set up your Scroll Bar as normal, setting the max limit to 100.
.
.
Step 2:
Write a formula to convert the Scroll Bar output to a percentile. For example:
=C2/100
.
.
Step 3:
Hide the Scroll Bar's output cell. In this case, we hid Column C.
.
.
And there it is.
I know…it's silly that you have to resort to basic math to make Scroll Bars work with Percentages.
Hopefully this will change in a future version of Excel.
OK Hari – I've answered your question. Now send me my cookies.
This reminds me of something I tried to do a while back and I just thought of the solution. I wanted a scrollbar to be able to range from -100 to +100 percent.
To do so I entered 0 as the minimum value and 200 as the maximum followed by this formula in D2:
=IF(C2=0;-100;IF(C2<100;-(100-C2);C2-100))/100
Thanks for the inspiration
m-b, I think you meant something like:
=if(C2=100,0,C2-100)/100
Unless I'm reading it wrong, your formula would #DIV/0 when C2 is 100.
JS, they both work but yours is much simpler. I knew I was making it too complicated
Thanks!
m-b: Great idea! I hadn't had the need to scroll to negative values, but I can see this being extremely valuable in some data models.
Who needs a formula to show the percentage? don't hide the linked cell under the scroll bar, show it. Use a number format like 0"%" and the whole number will appear as the corresponding percentage.
Jon: Ha. I never even thought abou that. Good one.