Solved

logarithmic scroll bar?

Posted on 2013-02-01
9
873 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
ID: 38844074
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
ID: 38845538
not really
did you try that?
is not working
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38845768
0
 
LVL 20

Author Comment

by:viki2000
ID: 38846131
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 38846166
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
ID: 38846235
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
ID: 38846244
I found it
Min scroll should be 483
0
 
LVL 20

Author Comment

by:viki2000
ID: 38846279
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
ID: 38847046
Thank you for the info and for your time.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Saved searches can save you time by quickly referencing commonly searched terms on any topic. Whether you are looking for questions you can answer or hoping to learn about a specific issue, a saved search can help you get the most out of your time o…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

911 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

25 Experts available now in Live!

Get 1:1 Help Now