Microsoft Office Tutorials and References

In Depth Information

**APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE**

STDEVPA(value1,value2,...) [Category: Statistical]]

Calculates standard deviation based on the entire population given as arguments,

including text and logical values. The standard deviation is a measure of how

widely values are dispersed from the average value (the mean).

STEYX(known_y’s,known_x’s) [Category: Statistical]]

Returns the standard error of the predicted y-value for each x in the regression.

The standard error is a measure of the amount of error in the prediction of y for

an individual x.

SUBSTITUTE(text,old_text,new_text,instance_num) [Category: Text]

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you

want to replace speciﬁ c text in a text string; use REPLACE when you want to

replace any text that occurs in a speciﬁ c location in a text string. Guru Tip: This

function is great for replacing a substring with another substring. Also great for

getting rid of strange characters. =SUBSTITUTE(A2,CHAR(160),CHAR(32)) will

replace all non-breaking spaces from web data with regular spaces. For another

example, see CODE. Also see page 120.

SUBTOTAL(function_num,ref1,ref2,...) [Category: Math]

Returns a subtotal in a list or database. It is generally easier to create a list

with subtotals using the Subtotals command (Data menu). Once the subtotal

list is created, you can modify it by editing the SUBTOTAL function. Guru Tip:

In layman’s terms, the SUBTOTAL function will sum all entries in a range but

will ignore other SUBTOTAL functions within the range. This allows you to add

multiple levels of subtotals in a range without affecting the grand total. Microsoft

ﬁ gured this functionality would be useful for SUM and the 10 other standard

calculation functions: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT,

STDDEV, STDDEVP, VAR, and VARP. Rather than adding 11 new functions

(such as SUBMAX, SUBAVERAGE…), they added a single function and allow

you to specify the calculation using the function_num. If you ever wondered

why the number 9 is used to represent the popular SUM function, it is because

SUM falls 9th alphabetically in the list when you are using the English version

of Excel. Function_num values from 1 to 11 perform the "classic" SUBTOTAL

calculation which includes visible and hidden rows. Using 101 to 111 performs

the "new" SUBTOTAL calculation which excludes hidden rows. Nothing you can

do will make it exclude hidden columns. Also see page 41.

SUM(number1,number2, ...) [Category: Math]

Adds all the numbers in a range of cells. Guru Tip: a workhorse function that is

probably the most used function in Excel. Use the Sigma icon or Alt+= to enter

a sum function. If you replace the comma with a space, the SUM will add up the

intersection of the ranges. For an example, see page 39.