R squared not the same in graph and spread sheet.

I have an X-Y plot displayed with a trend line equation and R squared. I 
also calculate R squared (RSQ) and the numbers are not the same; 0.8918 
and 0.928. Can someone explain this (Excel 2010 and Windows 7)?


-- 
Jim Silverton

Extraneous "not" in Reply To.
0
2/16/2012 3:25:59 PM
excel 39879 articles. 2 followers. Follow

18 Replies
739 Views

Similar Articles

[PageSpeed] 2

"James Silverton" <jim.silverton@verizon.net> wrote:
> I have an X-Y plot displayed with a trend line equation
> and R squared. I also calculate R squared (RSQ) and the
> numbers are not the same; 0.8918 and 0.928. Can someone
> explain this (Excel 2010 and Windows 7)?

You would need to post [1] more information for a dispositive explanation. 
But generally, there might be two causes for such a large difference:

1. You used RSQ with the original X-Y data points in the worksheet instead 
of with trendline X-Y data points.

2. If you used RSQ with trendline X-Y data points that you generated in the 
worksheet, you might have used rounded coefficients as they appear in the 
trendline equation instead of "exact" coefficients.

The remedy to #2 is to recover the "exact" coefficients in one of two ways. 
The quick-and-dirty way is to format the trendline equation so that 
coefficients are displayed with the Scientific format with 14 decimal 
places.  Then copy-and-pasted each coefficient into the worksheet.  The 
better method is to use LINEST to calculate the coefficients in the 
worksheet; or you might be able to use FORECAST, TREND or another similar 
function.  We need more information in order to tell you exactly how to do 
that.


-----
[1] If the data are not simple, it might be better to upload an example file 
to a file-sharing website and post the URL of the shared uploaded file here. 

0
joeu20041 (141)
2/16/2012 5:01:26 PM
On 2/16/2012 12:01 PM, joeu2004 wrote:
> "James Silverton" <jim.silverton@verizon.net> wrote:
>> I have an X-Y plot displayed with a trend line equation
>> and R squared. I also calculate R squared (RSQ) and the
>> numbers are not the same; 0.8918 and 0.928. Can someone
>> explain this (Excel 2010 and Windows 7)?
>
> You would need to post [1] more information for a dispositive
> explanation. But generally, there might be two causes for such a large
> difference:
>
> 1. You used RSQ with the original X-Y data points in the worksheet
> instead of with trendline X-Y data points.
>
> 2. If you used RSQ with trendline X-Y data points that you generated in
> the worksheet, you might have used rounded coefficients as they appear
> in the trendline equation instead of "exact" coefficients.
>
> The remedy to #2 is to recover the "exact" coefficients in one of two
> ways. The quick-and-dirty way is to format the trendline equation so
> that coefficients are displayed with the Scientific format with 14
> decimal places. Then copy-and-pasted each coefficient into the
> worksheet. The better method is to use LINEST to calculate the
> coefficients in the worksheet; or you might be able to use FORECAST,
> TREND or another similar function. We need more information in order to
> tell you exactly how to do that.
>
>
> -----
> [1] If the data are not simple, it might be better to upload an example
> file to a file-sharing website and post the URL of the shared uploaded
> file here.

Here are the data
X	Y
2	1.55
3	1.57
4	1.79
5	1.59
6	1.84
7	3.14
8	2.77
9	3.01
10	2.5
12	2.87
13	4.01
14	4.53
15	4.59
16	4.64
17	4.54
18	5.44
19	4.84
20	4.67
21	4.41
22	4.36
23	5.29
24	5.4
25	6.94
26	6.9
27	7.11
28	7.34
29	7.9
30	7.16
31	8.28
32	7.39
33	6.81

Is the fact that I am using straight lines to connect the points in the 
graph of importance?


-- 
Jim Silverton

Extraneous "not" in Reply To.
0
2/16/2012 6:22:56 PM
On Thu, 16 Feb 2012 13:22:56 -0500, James Silverton <jim.silverton@verizon.net> wrote:

>Here are the data
>X	Y
>2	1.55
>3	1.57
>4	1.79
>5	1.59
>6	1.84
>7	3.14
>8	2.77
>9	3.01
>10	2.5
>12	2.87
>13	4.01
>14	4.53
>15	4.59
>16	4.64
>17	4.54
>18	5.44
>19	4.84
>20	4.67
>21	4.41
>22	4.36
>23	5.29
>24	5.4
>25	6.94
>26	6.9
>27	7.11
>28	7.34
>29	7.9
>30	7.16
>31	8.28
>32	7.39
>33	6.81
>
>Is the fact that I am using straight lines to connect the points in the 
>graph of importance?

FYI, in Excel 2007, I get the same values for RSQ and displayed as r^2 on the chart  (0.921914019817796)

I plotted an x-y scatter chart, linear trendline
0
ron6368 (329)
2/16/2012 6:48:36 PM
"Ron Rosenfeld" <ron@nospam.net> wrote:
> On Thu, 16 Feb 2012 13:22:56 -0500, James Silverton 
> <jim.silverton@verizon.net> wrote:
[....]
> FYI, in Excel 2007, I get the same values for RSQ and
> displayed as r^2 on the chart  (0.921914019817796)
> I plotted an x-y scatter chart, linear trendline

RSQ of the original data and R^2 for the linear trendline should always be 
about the same, since RSQ is essentially used to construct the linear 
trendline.  See http://en.wikipedia.org/wiki/Simple_regression.

Some infinitesimal difference is possible due to floating-point anomalies. 
The degree of difference is likely to vary depending on the number of data 
of points and the range of the magnitude of the values. 

0
joeu20041 (141)
2/16/2012 7:28:51 PM
On Thu, 16 Feb 2012 11:28:51 -0800, "joeu2004" <joeu2004@foo.bar> wrote:

>RSQ of the original data and R^2 for the linear trendline should always be 
>about the same, since RSQ is essentially used to construct the linear 
>trendline. 

Oh, I only posted to indicate to the OP that I was not seeing a difference, using his data and Excel 2007.
I didn't realize that about RSQ and R^2.  I believe that prior to Excel 2007, MS was using a different, and inferior, algorithm for LINEST and the linear trendline generator in charting.
0
ron6368 (329)
2/16/2012 8:51:35 PM
"James Silverton" <jim.silverton@verizon.net> wrote:
> Here are the data
[....]
> Is the fact that I am using straight lines to connect
> the points in the graph of importance?

No.  But the type of trendline is.  You neglect to mention it.

We would expect RSQ of the original data and R^2 of the trendline to be 
about the same only if you use a __linear__ trendline.  Some relatively 
small difference is possible due to floating-point anomalies; that is, due 
to the way that Excel represents numbers internally.

But based on the difference you noted previously -- 0.8918 for one, 0.928 
for the other, not clear which is which -- I suspect you used an 
__exponential__ trendline.

For the data that you posted, RSQ returns 0.921914019817796, and R^2 for the 
exponential trendline is 0.894858644182441.

Note that the two R-squares are relatively close to what you mentioned 
previously.  I presume the difference is because you posted rounded data as 
they are displayed and the underlying actual values are slightly different. 
Or because you posted different data altogether.

In any case, assuming you used an exponential trendline of the form y = 
c*e^(b*x), you can compute the trendline coefficients b and c in Excel as 
follows:

B1:  =LINEST(LN(Y1:Y31),X1:X31)
C1:  =EXP(INDEX(LINEST(LN(Y1:Y31),X1:X31),1,2))

Then you can compute the corresponding data points along the trendline by 
putting the following formula into Z1 and copying down:

Z1:  =$C$1*EXP($B$1*X1)

Then R^2 might be computed by the formula =RSQ(Y1:Y31,Z1:Z31).  Note that 
RSQ(knownY,knownX) should not be taken literally to mean Y-axis and X-axis 
data.

However, RSQ returns 0.997576649432384, not 0.894858644182441.

I believe the explanation is:  there are several definitions of R-squared. 
The LINEST help page explains how R-squared is computed for LINEST. 
Off-hand, it seems different from RSQ.  (The two equations may or may not be 
mathematically equivalent for linear trendlines.  TBD.)

Frankly, I cannot duplicate the trendline R^2 even using the equations in 
the LINEST help page.  Perhaps I am making some dumb mistakes (TBD).

But you can recover the LINEST R^2 with the following formula:

=INDEX(LINEST(LN(B1:B31),A1:A31,,TRUE),3,1)

That returns 0.894858644182439.  The infinitesimal difference (2E-15) is 
probably due to floating-point anomalies in the apparently different 
implementations of the LINEST and trendline R^2 algorithms.

Not exactly a dispositive explanation (yet).  But at least I've given you 
the LINEST formulas that you can use to duplicate the chart trendline.

For the coefficients of other types of trendlines, see 
http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas. 

0
joeu20041 (141)
2/16/2012 9:28:03 PM
"joeu2004" <joeu2004@foo.bar> wrote:
> Then R^2 might be computed by the formula =RSQ(Y1:Y31,Z1:Z31).
[....]
  Note that
> However, RSQ returns 0.997576649432384, not 0.894858644182441.

__That__ was a dumb mistake -- a typo in some formulas.  That RSQ formula 
returns 0.879710250121746.

Still different from R^2 returns by LINEST and displayed for the exponential 
trendline.

But much closer. 

0
joeu20041 (141)
2/16/2012 9:39:00 PM
"Ron Rosenfeld" <ron@nospam.net> wrote:
> On Thu, 16 Feb 2012 11:28:51 -0800, "joeu2004" <joeu2004@foo.bar> wrote:
>> RSQ of the original data and R^2 for the linear trendline
>> should always be about the same, since RSQ is essentially
>> used to construct the linear trendline.
>
> Oh, I only posted to indicate to the OP that I was not
> seeing a difference, using his data and Excel 2007.

But Jim did not say he used a linear trendline, IMHO.

Jim wrote:  "Is the fact that I am using straight lines to connect the 
points in the
graph of importance?".

I suspect you interpreted that to refer a linear trendline.

I interpret that to be a description of the chart type that he used: 
straight lines between data points instead of a smooth curve or no 
connection at all.


Ron wrote:
> I believe that prior to Excel 2007, MS was using a different,
> and inferior, algorithm for LINEST and the linear trendline
> generator in charting.

You may or may not be correct about changes in the LINEST and/or trendline 
algorithms.  I don't recall.

But that seems irrelevant.  Jim said he is using XL2010.

And FYI, I get the "same" results for RSQ, LINEST and the trendline using 
XL2003.

(Actually, the RSQ result differs by one in the 15th significant digit.) 

0
joeu20041 (141)
2/16/2012 9:49:23 PM
On 2/16/2012 4:49 PM, joeu2004 wrote:
> "Ron Rosenfeld" <ron@nospam.net> wrote:
>> On Thu, 16 Feb 2012 11:28:51 -0800, "joeu2004" <joeu2004@foo.bar> wrote:
>>> RSQ of the original data and R^2 for the linear trendline
>>> should always be about the same, since RSQ is essentially
>>> used to construct the linear trendline.
>>
>> Oh, I only posted to indicate to the OP that I was not
>> seeing a difference, using his data and Excel 2007.
>
> But Jim did not say he used a linear trendline, IMHO.
>
> Jim wrote: "Is the fact that I am using straight lines to connect the
> points in the
> graph of importance?".
>
> I suspect you interpreted that to refer a linear trendline.
>
> I interpret that to be a description of the chart type that he used:
> straight lines between data points instead of a smooth curve or no
> connection at all.
>
>
> Ron wrote:
>> I believe that prior to Excel 2007, MS was using a different,
>> and inferior, algorithm for LINEST and the linear trendline
>> generator in charting.
>
> You may or may not be correct about changes in the LINEST and/or
> trendline algorithms. I don't recall.
>
> But that seems irrelevant. Jim said he is using XL2010.
>
> And FYI, I get the "same" results for RSQ, LINEST and the trendline
> using XL2003.
>
> (Actually, the RSQ result differs by one in the 15th significant digit.)

I will just say that I am using a linear trend line; the only type for 
which I want to use R^2.

I repeated the graph and spread sheet with the numbers I supplied (in 
case non-displayed places were a problem in my original spread sheet) 
and the two values of R squared are closer; 0.915 and 0.9219 but I don't 
see why there should be any difference if the same formula is being used.

-- 
Jim Silverton

Extraneous "not" in Reply To.
0
2/16/2012 10:41:53 PM
PS....  I wrote:
> Then R^2 might be computed by the formula =RSQ(Y1:Y31,Z1:Z31).
[....]
> However, RSQ returns 0.997576649432384, not 0.894858644182441.
[....]
> you can recover the LINEST R^2 with the following formula:
> =INDEX(LINEST(LN(B1:B31),A1:A31,,TRUE),3,1)

First, some errata....  In keeping with the other cell assignments that I 
used, that should be written as:

=INDEX(LINEST(LN(Y1:Y31),X1:X31,,TRUE),3,1)

Second, according to response #8 in 
http://www.ozgrid.com/forum/showthread.php?t=151617, R^2 should be computed 
by:

=RSQ(LN(Y1:Y31),X1:X31)

Although that is indeed the same as the value returned by LINEST, I will 
need to cogitate on it to understand why it should be.

Well, clearly LINEST is using the "correlation coefficient" definition of 
R^2, not the "coefficient of determination" definition of R^2 described in 
the LINEST help text.

I am not familiar with the mathematical and conceptual differences between 
the two.

0
joeu20041 (141)
2/16/2012 10:55:18 PM
"James Silverton" <jim.silverton@verizon.net> wrote:
> I will just say that I am using a linear trend line;
> the only type for which I want to use R^2.

Okay, GIGO.  For the future, learn to state all the relevant facts 
correctly.


Jim wrote:
> I repeated the graph and spread sheet with the numbers
> I supplied (in case non-displayed places were a problem
> in my original spread sheet) and the two values of R squared
> are closer; 0.915 and 0.9219 but I don't see why there
> should be any difference if the same formula is being used.

If you are saying that you copy-and-paste the numbers you posted into an 
Excel worksheet, as Ron and I had to do, and you get those two different 
numbers, I have no idea what mistake you are making.

Both Ron and I get the same results.  That is, RSQ, 
INDEX(LINEST(Y1:Y31,X1:X31),3,1) and the trenadline equation (formatted as 
Scientific with 14 decimal places) all return the same result at least to 
the 14th significant digit.

I am using XL2010 as you say you are.  I am using an Intel-compatible 
computer (in fact, an Intel computer), as I ass-u-me you are.

IMHO, the only way to unravel the problem is for you to upload an example 
Excel file to a file-sharing website and post the URL of the shared uploaded 
file here.  Here is a list of some free file-sharing websites; or use your 
own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

0
joeu20041 (141)
2/16/2012 11:56:57 PM
For posterity, albeit irrelevant now that Jim has clarified his 
situation....

I wrote previously:
> Then R^2 might be computed by the formula =RSQ(Y1:Y31,Z1:Z31).
> Note that RSQ(knownY,knownX) should not be taken literally
> to mean Y-axis and X-axis data.

Wrong!  RSQ is intended to be a measure of __linear_dependence__ between two 
sets of data, knownY and knownX.  In other words, there should be a linear 
relationship between the two.  So knownY and knownX should indeed by the 
Y-axis and X-axis coordinates of some function.  That is also the sense of 
R^2 displayed with a chart trendline.

In contrast, the "coeffiecient of determination", also called R^2 :-(, is a 
measure of fit between actual and predicted data.  In Excel, it might be 
written as =1 - SUMXMY2(Y1:Y31,Z1:Z31)/DEVSQ(Y1:Y31).  However, I am not 
sure if the theory behind the "coefficient of determination" permits it to 
be used a measure of fit between the actual data and an exponential 
trendline.


I wrote:
> Second, according to response #8 in 
> http://www.ozgrid.com/forum/showthread.php?t=151617, R^2 should be 
> computed by:
> =RSQ(LN(Y1:Y31),X1:X31)

Note that LN(Y1:Y31) is the log of the exponential trendline given by y = 
c*exp(m*x), where c = exp(b).  Thus, LINEST(LN(Y1:Y31),X1:X31) is the 
__linear__ function ln(y) = m*x + b.  So RSQ(LN(Y1:Y31),X1:X31) is a measure 
of the linear dependence between ln(y) and x.  And by commutativity, it is a 
measure of the exponential dependence between y and x.

(To be consistent with LOGEST documentation, that I use "m" where "b" was 
used previously.)


0
joeu20041 (141)
2/17/2012 12:39:23 AM
On Thu, 16 Feb 2012 13:49:23 -0800, "joeu2004" <joeu2004@foo.bar> wrote:

>
>But Jim did not say he used a linear trendline, IMHO.

Correct.  That is why I specified what trendline I used

>
>Jim wrote:  "Is the fact that I am using straight lines to connect the 
>points in the
>graph of importance?".
>
>I suspect you interpreted that to refer a linear trendline.

Your suspicion is incorrect.

>
>I interpret that to be a description of the chart type that he used: 
>straight lines between data points instead of a smooth curve or no 
>connection at all.

As did I.

>
>
>Ron wrote:
>> I believe that prior to Excel 2007, MS was using a different,
>> and inferior, algorithm for LINEST and the linear trendline
>> generator in charting.
>
>You may or may not be correct about changes in the LINEST and/or trendline 
>algorithms.  I don't recall.
>
>But that seems irrelevant.  Jim said he is using XL2010.

I was responding to your comment about RSQ being used to construct the linear trendline, and trying to confirm my thought that this was a change from pre-2007 versions.
>
>And FYI, I get the "same" results for RSQ, LINEST and the trendline using 
>XL2003.

That's interesting, and suggests that the change in the LINEST may have predated 2003.  Or maybe it just shows up with certain charting parameters

>(Actually, the RSQ result differs by one in the 15th significant digit.) 

Interesting.  In 2007, they are identical (formatted to 16 decimals)

0.9219140198177960
0.9219140198177960
0
ron6368 (329)
2/17/2012 2:09:15 AM
On Thu, 16 Feb 2012 13:49:23 -0800, "joeu2004" <joeu2004@foo.bar> wrote:

>(Actually, the RSQ result differs by one in the 15th significant digit.) 

Addendum:

In my use of Excel 2007, RSQ and the "chart" r^2 are identical to 15 decimal digits; the r2 coefficient from the LINEST equation is off by 1 in the fifteenth position.

0
ron6368 (329)
2/17/2012 2:19:09 AM
On Thu, 16 Feb 2012 21:19:09 -0500, Ron Rosenfeld <ron@nospam.net> wrote:

>On Thu, 16 Feb 2012 13:49:23 -0800, "joeu2004" <joeu2004@foo.bar> wrote:
>
>>(Actually, the RSQ result differs by one in the 15th significant digit.) 
>
>Addendum:
>
>In my use of Excel 2007, RSQ and the "chart" r^2 are identical to 15 decimal digits; the r2 coefficient from the LINEST equation is off by 1 in the fifteenth position.

0.9219140198177950	LINEST
0.9219140198177960	Chart Trendline
0.9219140198177960	RSQ


0
ron6368 (329)
2/17/2012 2:22:59 AM
On 2/16/2012 7:39 PM, joeu2004 wrote:
> For posterity, albeit irrelevant now that Jim has clarified his
> situation....
>
> I wrote previously:
>> Then R^2 might be computed by the formula =RSQ(Y1:Y31,Z1:Z31).
>> Note that RSQ(knownY,knownX) should not be taken literally
>> to mean Y-axis and X-axis data.
>
> Wrong! RSQ is intended to be a measure of __linear_dependence__ between
> two sets of data, knownY and knownX. In other words, there should be a
> linear relationship between the two. So knownY and knownX should indeed
> by the Y-axis and X-axis coordinates of some function. That is also the
> sense of R^2 displayed with a chart trendline.
>
> In contrast, the "coeffiecient of determination", also called R^2 :-(,
> is a measure of fit between actual and predicted data. In Excel, it
> might be written as =1 - SUMXMY2(Y1:Y31,Z1:Z31)/DEVSQ(Y1:Y31). However,
> I am not sure if the theory behind the "coefficient of determination"
> permits it to be used a measure of fit between the actual data and an
> exponential trendline.
>
>
> I wrote:
>> Second, according to response #8 in
>> http://www.ozgrid.com/forum/showthread.php?t=151617, R^2 should be
>> computed by:
>> =RSQ(LN(Y1:Y31),X1:X31)
>
> Note that LN(Y1:Y31) is the log of the exponential trendline given by y
> = c*exp(m*x), where c = exp(b). Thus, LINEST(LN(Y1:Y31),X1:X31) is the
> __linear__ function ln(y) = m*x + b. So RSQ(LN(Y1:Y31),X1:X31) is a
> measure of the linear dependence between ln(y) and x. And by
> commutativity, it is a measure of the exponential dependence between y
> and x.
>
> (To be consistent with LOGEST documentation, that I use "m" where "b"
> was used previously.)
>
>
Thanks to everyone and apologies for wasting time. I now know that the 
points I had selected for the graph were not the same as those used for 
calculating R^2 in the spreadsheet. I'm not quite sure how I did it but 
I think I was still attuned to methods I had used in Excel 2003 when I 
first set up the spread sheet. The numbers I posted to the ng do give 
R^2 = 0.9219 both on the chart and on the spreadsheet.

-- 
Jim Silverton

Extraneous "not" in Reply To.
0
2/17/2012 4:04:02 AM
On Thu, 16 Feb 2012 13:22:56 -0500, James Silverton wrote:
>  
> Is the fact that I am using straight lines to connect the points in the 
> graph of importance?

Are you forcing the regression to go through the origin?  That makes 
R� into nonsense.

-- 
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                   http://OakRoadSystems.com
Shikata ga nai...
0
2/18/2012 2:34:29 PM
On 2/18/2012 9:34 AM, Stan Brown wrote:
> On Thu, 16 Feb 2012 13:22:56 -0500, James Silverton wrote:
>>
>> Is the fact that I am using straight lines to connect the points in the
>> graph of importance?
>
> Are you forcing the regression to go through the origin?  That makes
> R� into nonsense.
>
I think I have been pointed to the reason for my troubles, see earlier, 
but no, I don't force the linear regression to go thro the origin. I 
don't actually use R^2 in any good statistical sense except as an 
indicator of variation.

-- 
Jim Silverton

Extraneous "not" in Reply To.
0
2/18/2012 3:36:09 PM
Reply:

Similar Artilces:

Printing Graphs in Money's Retirement Planner??
Folks, I don't often print my retirement planner from Money, but today I found need to while in the offices of a financial planner. I was surprised to find that no matter how I tried to print (FAX, Adobe PDF, direct to laser printer, Microsoft Office Document Image Writer), the planning graphs would show up as empty space on the printed page. I am pretty sure this used to work. It's not working now. Have I turned off some hidden function which says "do not print graphs", or is there some other fix to this? Thanks! ...

open at define work sheet
Iwant a shared workbook to always open at a define work sheet (Front page). I have entered the following in vb under workbook but it does not work. I entered the following Private Sub Workbook_Open(sheets "front page".select) End Sub please help Kitty Put the following code in the ThisWorkbook code module: Private Sub Workbook_Open() Me.Worksheets("Front Page").Select End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "kit" <kit@discussions.microsoft.com> wrote in message news:3FD800CE...

How do I display a custom date range for a graph in a report?
I can display a custom date range in my reports, but if I add a graph it goes into a loop when prompting for the date range. I have also tried to do the graph without the report but instead of showing the date range it shows ' #Name? ' where the date should be. Does anyone know how to get around this? Thank you, Diana ...

sheet tab
I want to increase font size of name 's sheet. What should I do?, Can you help me?,,Pls thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=38f461ab-99f6-47aa-b5ea-15e79e3d27c2&dg=microsoft.p...

Moving data between Excel sheets
"I have an excel spreadsheet that has 2 sheets, one has a list of open issues" and one a list of "closed issues". One of the fields on the "open issues" sheet has a "closed date" field. Whenever a date is typed into this field, I would like for it to be moved to the "closed issues" sheet, to avoid duplicate, manual entries being done. Can anyone help me with the best way to achieve this? Can this be achieved by using Macro's. Any help with this, would be greatly appreciated. Thanks! Suggest you send this to the programming newsgroup...

Is there a template for creating math practice sheets for add, su.
I want to create a practice sheet for specific tables (like 7's, 9's), and be able to get new problems and have answers. ...

Prinding each row of Excel sheet on a seperate page dispallying one record on each pa
From a spread sheet ,I need to print out a row on each page seperately that is printout each record on the row on one page seperately. Can any one please advise how it can be done ? Fro example Row 1 Data 11 Data 12 Data 13 Row 2 Data 21 Data 22 Data 23 Row 3 Data 31 Data 32 Data 33 I would want to print out Data 11 and Data 22 on one page then Data 21 and Data 22 on the second page and data 31 and Data 33 on the third page Pleae advise if and hwo this is possible. Thank you Sanjeev --- Message posted from http://www.ExcelForum.com/ Sorry ...

show value as percentage in stacked bar graph
I work for the Court and I am trying to show the percentage of 2 types of cases (CC and SP) to the total judicial caseload, in five different separate locations, on one graph. I developed a stacked chart which shows the values (number of cases) of each type (CC or SP) per location. I was also able to also show the value only of the total number of cases (CC + SP) and keep the original chart format without adding a third stack on top by entering a third series as the total value, but making this series transparent and aligning the total value at bottom of stacked series of CC & SP...

road graph
Please have extra patience for me please, i am very new for charts. from access data base with fields [city],[at_kilimeter],[from_kilometer],[to_kilometer],[gradient:-raising/falling],[Amount_gradient :-100,150 etc any number],[curves:-in degrees],[curve_direction:-left/right]. .......................................... i want to show 3 factors over the graph between two said kilometers (1) city lies between two said kilometers as text boxes (2) the geography of road between kilometers like road raising / falling (3) curves left/right ......... up and down i can able to show but i could not...

I would like to compress the x-axis of my graph
I would like to remove a section of the x-axis of my graph to read so that the axis reads 0,1,2,3..........10,11,12,13 instead of 0,1,2,3,4,5,6,7,8,9,10,11,12,13, One option: http://www.peltiertech.com/Excel/Charts/BrokenYAxis.html "red" <red@discussions.microsoft.com> wrote in message news:B32A0725-2338-4358-A8C7-9A42BC573BDC@microsoft.com... >I would like to remove a section of the x-axis of my graph to read so that > the axis reads 0,1,2,3..........10,11,12,13 instead of > 0,1,2,3,4,5,6,7,8,9,10,11,12,13, ...

Stacking Single Bar Graph
Need to find a graph that stacks a series of values in one bar. I know that the stacking graphs should work, but I can't seem to figure out how. For example, $100 total with $25 for Susie, $50 for Bill, $15 for Jane, and $10 for Mark. Each of these need to have separate manipulatable colors and text. Thanks in advance. Enter the names and amounts in adjacent columns on the worksheet: Susie 25 Bill 50 Jane 15 Mark 10 Select a cell in the list, and click the Chart Wizard button Select the Column chart type, and the Stacked column subtype Click Next On the Data Range tab, select Series ...

time sheet template available
I am looking for a bi-weekly time sheet template (or weekly). Having problems with excel correctly computing total times (works great for one day). bsydnes, heres one, http://www.cpearson.com/excel/overtime.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "bsydnes" <bsydnes@discussions.microsoft.com> wrote in message news:CA5250B0-8F92-41BB-BB5C-04F085C009F2@microsoft.com... > I am looking for a bi-weekly time...

CVS file only inserts commas for 15 lines of spread
Hi, I hava an user in Europe that is following the exact steps I am to covert a file to CSV format. When we open her file in word pad, only the first 15 lines of data have comms delimted. It happens every time regardless of how much data is in our spread. Any ideas on how to correct this? Thanks much! S I've never seen anything like this. If they reopen the .csv file in Excel, does it look ok? If they open the file in NotePad (not wordpad), does it look ok? My silly guess: I'm wondering if there is any special character in the data that is being used as an end of file marker....

Multi-sheet totals
Hi folks, I'm fairly new to the "joys" of Excel and would appreciate some advice. I have a multi-sheet workbook. Each sheet has a cell (the same cell on every sheet), let's call it "D2", with a "total" for that sheet. Is there a *simple* way of adding up all the "D2's" in the workbook and putting that sum on the last page? Ideally I want it to keep adding up all the "D2" cells, even when I add new sheets. At the moment I'm using the old =SUM('sheet1'!D2+'sheet2'!D2) trick, but I'm getting a bit sick of ma...

I need .5 instead of .3 on total time on time sheet
I need to make the total time at the end of the week show .5 (39.5 hours) not .3 (39.3 hours) What formula? What value of the source cells? What formatting? -- Kind Regards, Niek Otten Microsoft MVP - Excel "Anthonyt65" <Anthonyt65@discussions.microsoft.com> wrote in message news:0B4BFDEF-6625-42E9-ACC3-143C5C8C8832@microsoft.com... >I need to make the total time at the end of the week show .5 (39.5 hours) > not .3 (39.3 hours) try =MROUND(F2,0.5) -- Don Guillett SalesAid Software donaldb@281.com "Anthonyt65" <Anthonyt65@discussions.microsoft.c...

CRM System Tray Icon shows as a black square.
Having installed the CRM Outlook Client for Laptops, the system tray icon was working fine, but, after a few reboots (but no changes) now just shows as a black square. If I hover over it, it says CRM CLient - Online as usual, and doesnt appear to be affecting the operation of CRM at all, its just damn annoying. Has anyone else seen this behaviour? And/or got a fix for it? I've tried the old de/re install and that's made no difference what so ever. T. I finally fixed this by uninstalling CRM, and then manually removing all the CRM DLL's. Then a reboot and reinstall, and th...

Weighted Avg
I maintain a spreadsheet for a telephone hotline. We track total number of calls received (column F) as well as average time to answer the call (column J). To analyze the month's average time to answer, I use the following weighted average formula: SUMPRODUCT (J5:J26,F5:F26)/SUM(F5:26). I have 2 questions relating to this: 1) Is the above formula correct to determine the average speed to answer? I've assumed the weights to be the total number of calls received. 2) I have a different spreadsheet for each month (Jan, Feb, etc.). How can I properly show the weighted average as...

need to zoom in a particular region of graph
I need to zoom in a particular region of graph. Please advise. ...

Maintenance log sheet for a brush chipper.
How do I make a maintenance log sheet for a brush chipper.My boss wanted to keep track of when I greased bearings,changed oil,filters and other related stuff. I have no clue how to start to make this, Please help! Thanks I do a similar thing with my cars. In column A list all the tasks that you want to log. For each task, when you perform that task put the date in the cell to the right. When you do that task again, put the date in the first empty column to the right, and so on. HTH Otto "cooldog...

Trendline on stacked bar graph
Hello How can you add a trendline to a stacked bar graph? Thanks -- Joni ------------------------------------------------------------------------ Joni's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=13596 View this thread: http://www.excelforum.com/showthread.php?threadid=513635 Keep in mind that a stacked chart depicts a sum of one or more series, not just each series. Trendlines only work on individual series. You could either generate trendlines for each series on a non-stacked basis, or you could add dummy line chart series to the stacked column chart...

Excel has stopped showing graphs on seperate chart tabs
All chart only worksheets appear as the vacant background (as if no file is open) yet the worksheet tabs are all visible. Charts embedded in a worksheet show OK. This applies to all files created or historic 97-2003 type files. What's causing it and what can I do? ...

Misplacement of objects on a sheet
In Excel 2007 when loading a workbook (originally created in Excel 2000), objects like text boxes, rectangles, etc will frequently be placed on the sheet in the wrong place. I have posted an example at http://65.243.151.82. Notice in the "Correct" screenshot the row of buttons. You can click any of the buttons (which correspond to sheet names) and go to that worksheet. The current active worksheet ("WA" in the example) is indicated by the red background text box filling the space of the button (and circled in the example). In the "Incorrect" example, notic...

MS Excel expense sheet password lost
Hello. i'm trying to edit the a ms excel expense statement/re-imbursement template. unfortunately, it's password protected (and nobody at work knows what it is). does anyone know of a program that can crack an ms excel password or know a way of over coming this problem? all i want to do is duplicate, re-arrange, and rename the sheets. wil. Hi Wil check out http://www.mcgimpsey.com/excel/removepwords.html for information Cheers JulieD "Wil" <nospam@nospam.com> wrote in message news:Xns9622D5640ED93nospamnospamcom@207.46.248.16... > Hello. i'm trying ...

Data is being duplicated on multiple sheets + [Group] in the title
OK - so I've turned something on by mistake and all the data that I've entered in "sheet2" has been replicated in "sheet1". The [Group] marker in the title bar is a clue I'm sure, but I have no idea what I've done. Can anyone tell me what I've done and how to undo it? Thanks, Nick. Right click a sheet tab and select ungroup, save the workbook -- Regards, Peo Sjoblom (No private emails please) "Nick" <Nick@discussions.microsoft.com> wrote in message news:3961EB07-45C9-4607-8BE2-FE0073EBD20E@microsoft.com... > OK - so I'...

how do i set half an excell sheet to now scroll with the rest.
http://office.microsoft.com/en-us/templates/TC010186511033.aspx with that check book register. the top few lines do not scroll when i scroll teh page. how do i repeat that option Select the cell under the row and to the right of the column you want immobilized and then go to menu Window>Freeze Panes Regards, KL "greasepain37" <greasepain37@discussions.microsoft.com> wrote in message news:98690D8D-D895-4310-9878-BC84170D0E3D@microsoft.com... > http://office.microsoft.com/en-us/templates/TC010186511033.aspx with that > check book register. the top few lines do n...