Solved

logarithmic scroll bar?

Posted on 2013-02-01
9
854 Views
Last Modified: 2013-02-02
I have the chart from the attached Excel file.
On top of the chart there is a scroll bar.

The axes of the chart are logarithmic and the scroll bar generates linear values.
The blue/red lined do not move in the same way on the bottom as on the top of the chart.
That is annoying.

If the scroll bar would be logarithmic then I guess the problem would be solved.
Is it any chance for  logarithmic scroll bar in Excel?
Any other suggestions?
With VBA?
ABB-chart-emulation-2.xls
0
Comment
Question by:viki2000
  • 6
  • 3
9 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
Enter this formula in L1

=10^((J6-2000)/1000)

and change the limits for the scroll bar to
0 to 3000
0
 
LVL 20

Author Comment

by:viki2000
Comment Utility
not really
did you try that?
is not working
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
0
 
LVL 20

Author Comment

by:viki2000
Comment Utility
I use Excel 2003.
When you move the scroll bar on the left side there is an error as you can see in the picture below, thta's why I asked you first time if you really tried the suggested formula by scrolling the bar.

error
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
Comment Utility
I had been working on 2010 where this error did not show up.

For 2003 change the formula in L2 as well

=IF(AND(L1>=0.03,L1<=1.193),N1,IF(AND(L1>=1.194,L1<=6.548),N2,IF(AND(L1>=6.549,L1<=10),N3,N1)))
0
 
LVL 20

Author Comment

by:viki2000
Comment Utility
almost good
we need to stop the left side of the scroll to the point where the graphic line stops, which is around 0.031
0
 
LVL 20

Author Comment

by:viki2000
Comment Utility
I found it
Min scroll should be 483
0
 
LVL 20

Author Comment

by:viki2000
Comment Utility
The problem is solved.
I think is good to give some explanations for the other future possible readers.
Yesterday I was too tired to think proper.

A scroll bar is linear.
The graphic is logarithmic.

If we want the behavior of the blue/red lines to behave linear as we move the scroll bar then we need to change the values given by scroll bar in “inverse” of logarithmic, which is exponential. As we work here with logarithm in base 10, then also the power of 10 has to be used. The logarithm and exponential function “compensate”, “annihilate” each other and the result is a proportional visual perception.

When we look on the X axes of the graphic we see 4 regions:
-      one from 0.01 to 0.1
-      one from 0.1 to 1
-      one from 1 to 10

That means the scroll bar should have 4 regions which behave different for us, even if it is linear for the scroll bar. And that behavior should be power of 10. In each region we have to change the power of 10 with one more factor. We multiply with 10 more for each region.

Now to the mathematics:

We can choose the scroll bar regions as:

10
20
30
40

meaning min 10 to max 40, or for better precision we can increase 100 to 400 or even from 1000 to 4000.

Then we have:

1000 - - > we divide with 1000 - - > 1 – we apply power of 10 - - > 10 - - > we divide with 1000 - - > 0.01
2000 - - > we divide with 1000 - - > 2 – we apply power of 10 - - > 100 - - > we divide with 1000 - - > 0.1
3000 - - > we divide with 1000 - - > 3 – we apply power of 10 - - > 1000 - - > we divide with 1000 - - > 1
4000 - - > we divide with 1000 - - > 4 – we apply power of 10 - - > 10000 - - > we divide with 1000 - - >10

If we consider the of scroll bar number as “a” and the needed result as “b” then:
b = (10^(a/1000))/1000 = 10^(a/1000 – 3) = 10^((a-3000)/1000)

If the scroll bar goes from 0 to 3000 then after is divided with 1000 we get 0, 1, 2, 3 and after applying power of 10 we get 1, 10, 100, 1000 therefore as last operation we have to divide only with 100 and not with 1000.
We get then next formula:
b = (10^(a/1000))/100 = 10^(a/1000 – 2) = 10^((a-2000)/1000)

In my case b=L1 cell and a=J6 cell.

We can calculate then also the minimum required number of the scroll bar.
I needed on the axes the min 0.03.
Then we have 0.03=10^((a-2000)/1000) which goes log(0.03)=(a-2000)/1000 then a-2000=1000*log(0.03) and finally a=2000+1000*log(0.03)=477.1213
Or we just click on the scroll bar and when arrives on the desired point we read the x value.
In my case I made first the calculation and then I adjusted a bit around 477 reading finally the scroll bar and I got 483.

What do you say ssaqibh?
Is all right so?
0
 
LVL 20

Author Closing Comment

by:viki2000
Comment Utility
Thank you for the info and for your time.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Surprisingly, there is a lot to Gym battles, and I thought it would be helpful to share knowledge about all the ins and outs of this feature!
Whether you believe the “gig economy,” as it has been dubbed, is the next big economic paradigm shift (https://www.theguardian.com/commentisfree/2015/jul/26/will-we-get-by-gig-economy) or an overstated trend (http://www.wsj.com/articles/proof-of-a-g…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now