Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

logarithmic scroll bar?

Posted on 2013-02-01
9
Medium Priority
?
948 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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
This article is written by John Gates, CISSP. Gates, the SNUG President-Elect, currently holds the position of Manager of Information Systems at Lake Park High School in Roselle, Illinois.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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