Why Are Minor Gridlines in MS Excel Changing Values Automatically?

[Stevedye56]

Hey all,

I have a quick question about the minor gridlines in MS Excel. I have points that differ by
.00001 on the graph. However when I type this in, Excel automatically changes it to
.000036 every time. I wasn't sure if anybody knew if Open Office could solve this problem or if its just a useless battle.

Thanks in advance,

Steve

 

[FredGarvin]

Are you using an X-Y line plot or a X-Y scatter plot? Make sure it is a scatter plot. Never use the line plot (I have no idea why they insist on including it as an option).

 

[Stevedye56]

Are you using an X-Y line plot or a X-Y scatter plot? Make sure it is a scatter plot. Never use the line plot (I have no idea why they insist on including it as an option).

Yes, I am using the X-Y scatter plot.

 

[FredGarvin]

Can you share some of the data so we can replicate the problem? I have never seen Excel do this. That definitely doesn't mean there's no bugs in there.

I just tried some junk data I made up and had no problem setting the minor plot divisions to .00001.

 

[Stevedye56]

Ok so this is a projectile motion problem and the y-axis represents displacement, "s" and the x-axis represents t^2/2 where "t" is time in seconds, and s is in meters.

s1= -0.385 t1= .27
s2= -0.352 t2= .26
s3= -0.318 t3= .24
s4= -0.286 t4= .23
s5= -0.257 t5= .22
s6= -0.240 t6= .21


I then use an equation in excel for it to caclulate t^2/2 for me automatically. =((B1^2)2)
I did this for each time. The results i got were this

t^2/2 1. 0.03645
t^2/2 2. 0.0338
t^2/2 3. 0.0288
t^2/2 4. 0.02645
t^2/2 5. 0.0242
t^2/2 6. 0.02205


Then I used the X-Y Scatter to make my graph which was fine. And then I added a linear trendline which was also fine; but when I tried to set my gridlines at .00001 they didin't work.

I hope the data helps a little.

Thanks,
Steve

 

[FredGarvin]

I see what you mean. However, what I am wondering now is really why you need that kind of resolution in the grid lines. None of the data you provided had a delta of .00001. Granted you may not have given all the data, but do you really need to have minor divisions that small?

It is definitely a resolution issue. If you narrow the overall range on the axis to a smaller value, you can get the minor divisions to what you are looking for. Perhaps Excel has a built in check to allow only so many minor divisions per overall scale.

 

[Stevedye56]

That was all the data I was given. I wasn't there for the class becasue I was ill but I figured I'd do the graph as an attempt to make up some work. My teacher wants each point on the graph to be crossed by both a minor x and minor y gridline. That's the only reason why I was trying to get them that small.

 

[FredGarvin]

That is a silly request from a teacher. There's no need for that. That is like saying that you want all of the T's in your paper to have little curls at the end. It makes no difference in the data.

I don't know what to tell you on this one. It looks like a limit in Excel itself. Perhaps someone else here can clue us both in.

 

[NoTime]

I don't know Excel well enough to try your sample.
Most microsoft drawing objects (like grid) have a link to screen resolution.
So for a particular screen resolution there will be a minimum setting for grid lines.
You could test this idea by choosing a lower screen setting and see if the number gets larger.

 

[Maninatters]

Im new to physicsforums, thought i could help.
I tried your data and sure enough i got the same problem, but the value to which excel changed the minor gridlines was different, and a little fiddling around later, i got the reason.
Its related to the "span" of your graph. Try limiting the values of the minimum and maximum values of x and y on the graph so that the range covered is less,and you'll see that excel rounds it off to a lower value. It seems as if excel changes the value automatically to the minimum it can display (it needs atleast this much separation between the gridlines).

Hope this helps

Mani