Solved

logarithmic scroll bar?

Posted on 2013-02-01
9
881 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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