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.