Graphing points to the 10^-19

[Lala]

I have the home and student 2007 version. When graphing a scatter graph with
the Y axis having 10 to the negative power, excel doesn't show the values on
the y axis. It only shows the 0 intercept value. I've tried editing the axis
values but the number still doesn't display and excel automatically puts the
number to at least 10 decimal places, ie 5.000000000000001E-19.

When I just click automatic, the right values appear in the edit menu but
the y values still won't show on the actual graph. So when doing a trendline
the equation doesn't show an m slope value, ie y=5E-19. A friend graphed my
data on an older version excel and it graphed it properly with the proper y
values displaying, and linear trendline equation was in right format of y =
mx + b, ie y= -2E-18x + 5E-19

Could my software be defective? Or are some features disabled?

 

[Bernard Liengme]

It would be helpful to see at least 5 data points (both the x and the y
values): could you list them for us?
Are you sure you have made an XY chart and not a Line chart?
If you wish you may send me a file with the data
best wishes

 

[Lala]

Yes the graph choosen is the xy scatter graph. The values I'm trying to graph
are :
x = 0.1111 y= 2.872*10^-19 ; (2.872E-19 when actually posting in the cell)
x = 0.0625 y= 4.043*10^-19 ; (4.043E-19)
x = 0.0400 y= 4.576*10^-19 ; (4.576E-19)
x = 0.0277 y= 4.814*10^-19 ; (4.814E-19)

Again the Y values of (1E-19 to 6E-19) are not posting, only 0 value is
posting.
When editing the y axis the minimum value on automatic is 0 and the maximum
does say 6.0E-19. But on the actual agraph I don't see these values. So when
doing a line graph I only get a linear equation of y=5E-19 instead of y=mx+b.

Excel does graph the data points and when adding data labels, the values
appear. It's just the division values (1.00E-19 to 6.00E-19) on the y axis
that doesn't appear.

I've also tried setting the cells to scientific numbers, general and numbers
but that didn't help.

It has to be the program because I've had 2 people graph my data and it
worked on their software.

 

[Bernard Liengme]

I had no trouble getting a chart with your data. The slope is -2.35E-18 and
intercept 5.49E-19
Since your cells have entries such as =2.872*10^-19 which display as
2.872E-19, it would appear you have real numbers and not text. A bit of a
mystery!

You have not set the y scale min & max to anything other than 'automatic'
have you?

What do you get with =SLOPE(B2:B5, A2:A5) - the B range holds the y-values,
A range the x-values. ?

Have you tried a new workbook entering values like this
x y
1 1E-10
2 2E-10
3 3E-10
4 4E-10
can you get a chart with these values?


By the way: if I was working with numbers like yours (but as a scientist I
cannot think of anything that small that I could measure!) I would use
x = 0.1111 y= 2.872
x = 0.0625 y= 4.043
x = 0.0400 y= 4.576
x = 0.0277 y= 4.814
Then I would scale the resulting slope and intercept by a factor of 10^-19

Happy to look at a file if you send me one to my private email (not
newsgroup)
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

 

[Jon Peltier]

Without doing anything special, I created a chart and added a trendline. It
worked as expected, and I got this formula:

y = -2.3507E-18x + 5.4943E-19
R2 = 9.9921E-01

This worked the same in Excel 2000 and in 2003. Then I tried it in Excel
2007 and it failed in the same manner that you described. The Y axis only
showed 0.0000E00 at the bottom, and no more values, and the formula showed Y
= 5.4943E-19, omitting the constant. Excel apparently thought this was a
rounding error and coerced it to zero. I ran a set of trials where I
multiplied the Y values of each trial by ten to get the Y values for the
next. My regression formulas looked like:

y = 5.49429E-19 [R² = 9.99213E-01]

y = 5.49429E-18 [R² = 9.99213E-01]

y = 5.49429E-17 [R² = 9.99213E-01]

y = 5.49429E-16 [R² = 9.99213E-01]

y = -2.35066E-14x [R² = 9.99213E-01]

y = -2.35066E-13x + 5.49429E-14 [R² = 9.99213E-01]

y = -2.35066E-12x + 5.49429E-13 [R² = 9.99213E-01]

y = -2.35066E-11x + 5.49429E-12 [R² = 9.99213E-01]


The first formula came from your example. Note that the constant has the
same pre-exponential factor for all of the formulas (5.49429) except for the
case when it would be 5.49429E-15, which sounds like a threshold for being
considered a rounding error. In all cases where the coefficient of X wasn't
ignored, the coefficient's pre-exponential factor was the same (-2.35066),
and in all cases, R² was the same (0.999213). I repeated this in Excel 2003,
and the formulas were identical except for the exponents of the fitting
constants.

I'd classify this one more as a bug than as a feature.

- Jon

 

[Jon Peltier]

The secret was in the OP's first line:

Microsoft has "improved" their treatment of tiny rounding errors by
arbitrarily assuming certain small values are really supposed to be zero.
The ignored values are less then or around 1E-15, which corresponds to the
missing values in my testing (see my other post). This has messed up the
trendline regression formula, which used to be considered the best in the
business.

LINEST calculates the regression coefficients properly, at least in 2003 and
2007 (I didn't bother to test prior to this).

- Jon

 

[Bernard Liengme]

Hi Jon,
Yes, I overlooked the mention of 2007! But as I said to OP, if is hard to
imagine a use of such small numbers and a y-axis transformation will work
even in XL2007. As you know better than I, MS have been trying hard to avoid
the 'nasty' tiny numbers that should be zero but are not because of 'binary
round off errors'. We getting fewer questions about that topic with XL2003
becoming more popular. So I cannot fault MS too much on this score!
Cheers

 

[Jerry W. Lewis]

Pardon my ignorance, but what changed in this regard with Excel 2003? I am
only aware of the unfortunate (IMHO) fuzz factor introduced in Excel 97 (MS
calls it an "optimization")
http://support.microsoft.com/kb/78113
with the result that =a-b and =(a-b) are no longer guaranteed to give the
same result. It probably did avoid a few easy questions about "why isn't my
result zero", but created a whole new genre of harder questions dealing with
the fundamental (in)consistency of Excel's arithmetic.

Jerry

 

[Jerry W. Lewis]

This is not a numerically challenging calculation, so LINEST in Excel 2000
agrees with the chart trendline to 15 figures.

I do wish that MS would stop digging this hole deeper.

Jerry

 

[Jon Peltier]

Amazing, isn't it? I reported it, but I doubt I'm the first.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

This is not a numerically challenging calculation, so LINEST in Excel 2000
agrees with the chart trendline to 15 figures.

I do wish that MS would stop digging this hole deeper.

Jerry

 

[Bernard Liengme]

Maybe I misspoke but it seems the newsgroup gets fewer question on this
topic now. I had thought XL2003 had improved the "optimization" Was is
really as long ago as XL97! Sorry for the confusion.
best wishes

 

[Lala]

Changed y scale min and maxt to fixed, thinking that might help, when nothing
happened changed it back to automatic. As for the numbers being small. The
graph was supppose to give me the Rydberg constant (from the slope) based on
a spectroscopy reading of a hydrogen light.

Thanks for all the help and giving me the full y formula values. The other
post about excel just assuming that any number smaller than 10^-10 is 0
answered my main question about the disappearing y values.

 

[Lala]

Yes thank you, it is a software issue. I thought I was doing something wrong
/ human error in inputing data or formatting the graph.

I hope this bug gets fixed because I'd rather not get another version of
excel and I might need to graph data with numbers this small again.

Anyway to get automatically notified should a patch for this bug gets issued?
Thanks for all the responses/help.

Without doing anything special, I created a chart and added a trendline. It
worked as expected, and I got this formula:

y = -2.3507E-18x + 5.4943E-19
R2 = 9.9921E-01

This worked the same in Excel 2000 and in 2003. Then I tried it in Excel
2007 and it failed in the same manner that you described. The Y axis only
showed 0.0000E00 at the bottom, and no more values, and the formula showed Y
= 5.4943E-19, omitting the constant. Excel apparently thought this was a
rounding error and coerced it to zero. I ran a set of trials where I
multiplied the Y values of each trial by ten to get the Y values for the
next. My regression formulas looked like:

y = 5.49429E-19 [R² = 9.99213E-01]

y = 5.49429E-18 [R² = 9.99213E-01]

y = 5.49429E-17 [R² = 9.99213E-01]

y = 5.49429E-16 [R² = 9.99213E-01]

y = -2.35066E-14x [R² = 9.99213E-01]

y = -2.35066E-13x + 5.49429E-14 [R² = 9.99213E-01]

y = -2.35066E-12x + 5.49429E-13 [R² = 9.99213E-01]

y = -2.35066E-11x + 5.49429E-12 [R² = 9.99213E-01]


The first formula came from your example. Note that the constant has the
same pre-exponential factor for all of the formulas (5.49429) except for the
case when it would be 5.49429E-15, which sounds like a threshold for being
considered a rounding error. In all cases where the coefficient of X wasn't
ignored, the coefficient's pre-exponential factor was the same (-2.35066),
and in all cases, R² was the same (0.999213). I repeated this in Excel 2003,
and the formulas were identical except for the exponents of the fitting
constants.

I'd classify this one more as a bug than as a feature.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Yes the graph choosen is the xy scatter graph. The values I'm trying to
graph
are :
x = 0.1111 y= 2.872*10^-19 ; (2.872E-19 when actually posting in the cell)
x = 0.0625 y= 4.043*10^-19 ; (4.043E-19)
x = 0.0400 y= 4.576*10^-19 ; (4.576E-19)
x = 0.0277 y= 4.814*10^-19 ; (4.814E-19)

Again the Y values of (1E-19 to 6E-19) are not posting, only 0 value is
posting.
When editing the y axis the minimum value on automatic is 0 and the
maximum
does say 6.0E-19. But on the actual agraph I don't see these values. So
when
doing a line graph I only get a linear equation of y=5E-19 instead of
y=mx+b.

Excel does graph the data points and when adding data labels, the values
appear. It's just the division values (1.00E-19 to 6.00E-19) on the y axis
that doesn't appear.

I've also tried setting the cells to scientific numbers, general and
numbers
but that didn't help.

It has to be the program because I've had 2 people graph my data and it
worked on their software.

 

[Katie]

I am having a similar problem. I am using excel 2007 to make calibration
curves using scatter plots. When I add a linear trendline, the equation
displayed is incorrect in 2007, however when plotted on an older version, the
equation is correct. I have found a way to "circumvent" the problem,
formatting the numbers in the lable to have decimals (2 places) the equation
corrects itself, however, I am left with an R2 value with only 2 decimal
places. I noticed this error after wasting a good days work using the wrong
equations given in 2007!!!!!

I too thought I was going nuts! The concentrations calculated from the
equation was completely wrong from the concentrations obtained when
extrapolating from the line!!!!!

Microsoft needs to fix these problems fast!!!!!!!!

Yes thank you, it is a software issue. I thought I was doing something wrong
/ human error in inputing data or formatting the graph.

I hope this bug gets fixed because I'd rather not get another version of
excel and I might need to graph data with numbers this small again.

Anyway to get automatically notified should a patch for this bug gets issued?
Thanks for all the responses/help.

Without doing anything special, I created a chart and added a trendline. It
worked as expected, and I got this formula:

y = -2.3507E-18x + 5.4943E-19
R2 = 9.9921E-01

This worked the same in Excel 2000 and in 2003. Then I tried it in Excel
2007 and it failed in the same manner that you described. The Y axis only
showed 0.0000E00 at the bottom, and no more values, and the formula showed Y
= 5.4943E-19, omitting the constant. Excel apparently thought this was a
rounding error and coerced it to zero. I ran a set of trials where I
multiplied the Y values of each trial by ten to get the Y values for the
next. My regression formulas looked like:

y = 5.49429E-19 [R² = 9.99213E-01]

y = 5.49429E-18 [R² = 9.99213E-01]

y = 5.49429E-17 [R² = 9.99213E-01]

y = 5.49429E-16 [R² = 9.99213E-01]

y = -2.35066E-14x [R² = 9.99213E-01]

y = -2.35066E-13x + 5.49429E-14 [R² = 9.99213E-01]

y = -2.35066E-12x + 5.49429E-13 [R² = 9.99213E-01]

y = -2.35066E-11x + 5.49429E-12 [R² = 9.99213E-01]


The first formula came from your example. Note that the constant has the
same pre-exponential factor for all of the formulas (5.49429) except for the
case when it would be 5.49429E-15, which sounds like a threshold for being
considered a rounding error. In all cases where the coefficient of X wasn't
ignored, the coefficient's pre-exponential factor was the same (-2.35066),
and in all cases, R² was the same (0.999213). I repeated this in Excel 2003,
and the formulas were identical except for the exponents of the fitting
constants.

I'd classify this one more as a bug than as a feature.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Yes the graph choosen is the xy scatter graph. The values I'm trying to
graph
are :
x = 0.1111 y= 2.872*10^-19 ; (2.872E-19 when actually posting in the cell)
x = 0.0625 y= 4.043*10^-19 ; (4.043E-19)
x = 0.0400 y= 4.576*10^-19 ; (4.576E-19)
x = 0.0277 y= 4.814*10^-19 ; (4.814E-19)

Again the Y values of (1E-19 to 6E-19) are not posting, only 0 value is
posting.
When editing the y axis the minimum value on automatic is 0 and the
maximum
does say 6.0E-19. But on the actual agraph I don't see these values. So
when
doing a line graph I only get a linear equation of y=5E-19 instead of
y=mx+b.

Excel does graph the data points and when adding data labels, the values
appear. It's just the division values (1.00E-19 to 6.00E-19) on the y axis
that doesn't appear.

I've also tried setting the cells to scientific numbers, general and
numbers
but that didn't help.

It has to be the program because I've had 2 people graph my data and it
worked on their software.

:

It would be helpful to see at least 5 data points (both the x and the y
values): could you list them for us?
Are you sure you have made an XY chart and not a Line chart?
If you wish you may send me a file with the data
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

I have the home and student 2007 version. When graphing a scatter graph
with
the Y axis having 10 to the negative power, excel doesn't show the
values
on
the y axis. It only shows the 0 intercept value. I've tried editing the
axis
values but the number still doesn't display and excel automatically
puts
the
number to at least 10 decimal places, ie 5.000000000000001E-19.

When I just click automatic, the right values appear in the edit menu
but
the y values still won't show on the actual graph. So when doing a
trendline
the equation doesn't show an m slope value, ie y=5E-19. A friend
graphed
my
data on an older version excel and it graphed it properly with the
proper
y
values displaying, and linear trendline equation was in right format of
y
=
mx + b, ie y= -2E-18x + 5E-19

Could my software be defective? Or are some features disabled?

 

[JonTarg]

I have noticed numerous axis labelling errors in the student version of Excel
2007; forced reformatting of number values being among them. Label rounding
and placement errors also appear commonplace.

Does Microsoft have any Excel 2007 fixes on the way?