Solved

logarithmic scroll bar?

Posted on 2013-02-01
9
907 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 21

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 21

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
 
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 21

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 21

Author Comment

by:viki2000
ID: 38846244
I found it
Min scroll should be 483
0
 
LVL 21

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 21

Author Closing Comment

by:viki2000
ID: 38847046
Thank you for the info and for your time.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Where to go on the main page to find the job listings. How to apply to a job that you are interested in from the list that is featured on our Careers page.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

742 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