Microsoft Knowledge Base Wiki

Note The trendline formula should only be used when your chart is an XY Scatter chart. This chart plots both the X axis and the Y axis as values. Line, Column, and Bar charts plot only the Y axis as values. The X axis is plotted only as a linear series in these chart types, regardless of what the labels actually are. Therefore, the trendline will be inaccurate if displayed on these types of charts. This behavior is by design.

1. In the chart, select the trendline equation.
2. On the Format menu, click Selected Data Labels.
3. Click the Number tab, and then click Number in the Category list.
4. In the Decimal places box, increase the number of decimal places to 30 so that you can see all the decimal places.
5. Click OK.

• You edit an Excel workbook that is saved on a SharePoint site that is running Excel Services in Microsoft Office SharePoint Server 2010.
• The workbook contains a PivotTable.
• You press ENTER to expand the PivotTable, or you press ENTER to collapse the PivotTable.

In this scenario, the PivotTable does not expand or it does not collapse.

To collapse a PivotTable in Excel Services in SharePoint Server 2010, press ALT+UPARROW.

• You have a server that is running Excel Services in Microsoft Office SharePoint Server 2010.
• On this server, you have a Microsoft Excel workbook that is saved to a SharePoint site.
• You edit the workbook.
• You type a new value into a cell that contains an array formula.

In this scenario, the contents of the cell changes to show to the new value and then the contents of the cell changes back to the array formula. However, you do not receive an error message.

1. Open the Excel workbook in the client version of Microsoft Excel 2010.
2. Make the change to the cell value.
3. Save the Excel workbook to the SharePoint site.

Enter the following formulas into cell E2 (or any
available blank cell):

=OFFSET(C2,2,-1,1,1)

• C2 – The referenced cell.
• 2 – Indicates the number of rows to move. Positive numbers mean
  move down, and negative numbers mean move up.
• -1 – Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
• 1 (second last value) – (Optional.) Indicates how many rows of data to return. This
  number must be a positive number.
• 1 (last value) – (Optional.) Indicates how many columns of data to return. This
  number must be a positive number.

Examples:

When you use this formula, the OFFSET function
returns the value of the cell that is located two rows down (2) and 1 row to
the left (-1) of cell C2 (which is cell B4). The value in cell B4 is “101″.
Therefore, the formula returns “101″.

For additional information about annual compounding, click the article number below
to view the article in the Microsoft Knowledge Base:

Calculating Future Value of Intra-Year Compound Interest

Intra-year compound interest is interest that is compounded more frequently than
once a year. Financial institutions may calculate interest on bases of
semiannual, quarterly, monthly, weekly, or even daily time periods.

Microsoft Excel includes the EFFECT function in the Analysis ToolPak add-in for versions older than 2003. The Analysis ToolPak is already loaded. The EFFECT function returns the compounded interest rate based on the annual interest rate and the number of compounding periods per year.

The formula to calculate intra-year compound interest with the EFFECT worksheet function is as follows:

=P+(P*EFFECT(EFFECT(k,m)*n,n))

The general equation to calculate compound interest is as follows

=P*(1+(k/m))^(m*n)

where the following is true:

P = initial principal
k = annual interest rate paid
m = number of times per period (typically months) the interest is
compounded
n = number of periods (typically years) or term of the loan

Examples

The examples in this section use the EFFECT function, the general
equation, and the following sample data:

   Intra-Year                 Number of compounding
   compounding rate          periods per year
   -----------------------------------------------
   Semiannual                  2
   Quarterly                   4
   Monthly                    12
   Weekly                     52
   Daily                     360 or 365(actual)
				

An investment of $100 pays 8.00 percent compounded semiannually. If the money is left in the account for three years, how much will the $100 be worth?

Use the EFFECT Worksheet Function

Because of semiannual compounding, you must repeat the EFFECT function twice to calculate the semiannual compounding periods. In the following example, the result of the nested function is multiplied by 3 to spread out (annualize) the compounded rate of over the term of the investment:

=100+(100*EFFECT(EFFECT(.08,2)*3,3))

The example returns $126.53.

Using the General Equation

The following example uses the general equation:

=100*(1+.08/2)^(2*3)

The example returns $126.53.

Calculate Interest Rates for Intra-Year Compounding

You can find the compounded interest rate given an annual interest rate
and a dollar amount.

The EFFECT worksheet function uses the following formula:

=EFFECT(EFFECT(k,m)*n,n)

To use the general equation to return the compounded interest rate, use
the following equation:

=(1+(k/m))^(m*n)-1

Examples

Use the EFFECT Worksheet Function

An investment of $100 pays 7.50 percent compounded quarterly. The money is left in the account for two years, for example. The following formula returns the compounded interest rate:

=EFFECT(EFFECT(.075,4)*2,2)

The example returns 16.022 percent.

Use the General Equation

The following equation returns the interest rate:

=(1+(.075/4))^(4*2)-1

Simple Concatenation

The following formula combines data from cells A1, B1 and C1. Write this formula into another column, such as column D1:

=A1&B1&C1

Add Spaces Between Each Column

The following formula combines data from cells A1, B1 and C1 and adds spaces between each set of data. Write this formula into another column, such as column D1:

=A1&” “&B1&” “&C1

Message 1

Message 2

Message 3

Message 4

Message 5

Message 6

If this
process is interrupted, your file may not be saved properly or you may find one
or more temporary files in the folder where you tried to save your file. In
addition, you may receive one of several alert or error messages.

The following information lists some of the possible messages you may receive
and explanations as to why you may receive them.

“Cannot access read-only document “

The drive you are saving to is a network drive and you do not
have sufficient privileges to save to that drive. Save to your local hard disk
drive or to a floppy disk drive.

“Disk is full”

The drive you are saving to does not have enough space for the
temporary file. Delete some files from the current drive and save to another
drive or to a floppy disk drive.

“Document not saved,” or “Document not completely saved”

The process was interrupted while creating the temporary file
because you pressed ESC, there was some hardware or software failure, or there
was another media problem. The original file is still intact and ,unless your
computer or workstation failed, the version of the file containing your current
changes is still open in memory. Save the file to an alternate drive. If your
computer or workstation did fail, you can use the original file. (Note that any
changes made in the last revision will be lost.)

“Document not saved. Any previously saved copy has been deleted”

The process was interrupted while deleting the original or
renaming the temporary file. This problem will occur for the same reasons as
the scenario described in the “‘Document not saved,’ or ‘Document not
completely saved’” section. In this case, your original file is gone (though
the temporary file may be readable). If your computer or workstation failed,
use the temporary file. If the interruption was caused by something else, the
version of the file containing your current changes is still open in memory.
Save the file to an alternate drive.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

The battery of tests is
named Diehard (see note 1). The algorithm that is implemented in Excel 2003 was
developed by B.A. Wichman and I.D. Hill (see note 2 and note 3). This random
number generator is also used in the RAT-STATS software package that is
provided by the Office of the Inspector General, U.S. Department of Health and
Human Services. It has been shown by Rotz et al (see note 4) to pass the
DIEHARD tests and additional tests developed by the National Institute of
Standards and Technology (NIST, formerly National Bureau of Standards).

Notes

• The tests were developed by Professor George Marsaglia,
  Department of Statistics, Florida State University and are available at the
  following Web site:
• Wichman, B.A. and I.D. Hill, Algorithm AS 183: An Efficient and Portable Pseudo-Random Number Generator, Applied Statistics, 31, 188-190, 1982.
• Wichman, B.A. and I.D. Hill, Building a Random-Number Generator, BYTE, pp. 127-128, March 1987.
• Rotz, W. and E. Falk, D. Wood, and J. Mulrow, A Comparison of Random Number Generators Used in Business, presented at Joint Statistical Meetings, Atlanta, GA,
  2001.

The basic idea is that if you take three random numbers on 0,1 and sum them, the fractional part of the sum is itself a random number on 0,1. The critical
statements in the Fortran code listing from the original Wichman and Hill
article are:

C IX, IY, IZ SHOULD BE SET TO INTEGER VALUES BETWEEN 1 AND 30000 BEFORE FIRST ENTRY

IX = MOD(171 * IX, 30269)

IY = MOD(172 * IY, 30307)

IZ = MOD(170 * IZ, 30323)

RANDOM = AMOD(FLOAT(IX) / 30269.0 + FLOAT(IY) / 30307.0 + FLOAT(IZ) / 30323.0, 1.0)

Therefore IX, IY, IZ generate integers between 0 and 30268, 0 and
30306, and 0 and 30322 respectively. These are combined in the last statement
to implement the simple principle that was expressed earlier: if you take three
random numbers on 0,1 and sum them, the fractional part of the sum is itself
a random number on 0,1.

Because RAND produces pseudo-random numbers,
if a long sequence of them is produced, eventually the sequence will repeat
itself. Combining random numbers as in the Wichman-Hill procedure guarantees
that more than 10^13 numbers will be generated before the repetition begins.
Several of the Diehard tests produced unsatisfactory results with earlier
versions of RAND because the cycle before numbers started repeating was
unacceptably short.

Results in Earlier Versions of Excel

The RAND function in earlier versions of Excel was fine in
practice for users who did not require a lengthy sequence of random numbers
(such as a million). It failed several standard tests of randomness, making its
performance an issue when a lengthy sequence of random numbers was needed.

Results in Excel 2003

A simple and effective algorithm has been implemented. The new
generator passes all standard tests of randomness.

Conclusions

Power users of RAND who require lengthy sequences of random numbers are better off with the new generator of Excel 2003 and later versions. Other users should be undeterred from using RAND in earlier versions of Excel.

For more information about an issue that was documented to occur in
RAND, click the following article number to view the article in the Microsoft Knowledge Base:

Consider the following example:

A shipment of 10 cases of widgets costs $0.20 per case. Because of heavy consumption of widgets, a second shipment of 40 cases now costs $0.30 per case.

The average cost of the cases in each shipment — determined by the formula ($0.20+$0.30)/2 = $0.25 — is not an accurate measure of the average cost of the cases because it does not take into account that there are 30 more cases being purchased at $0.30 than at $0.20. The weighted average would return $0.28, a more accurate representation of the average cost of a case of widgets in these two shipments.

1. In a new worksheet, type the following data:

      A1:  Cost     B1:  Cases
      A2:  $.20     B2:  10
      A3:  $.30     B3:  40
   					

2. Type the formula below in any blank cell (it is not necessary to type this formula as an array):
   =SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3)

   or

   =((A2*B2)+(A3*B3))/SUM(B2:B3)