Microsoft Office Tutorials and References
In Depth Information
APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE
GETPIVOTDATA(pivot_table,name) [Category: Database]
Returns data stored in a PivotTable report. You can use GETPIVOTDATA to
retrieve summary data from a PivotTable report, provided the summary data
is visible in the report. Guru Tip: While a few people swear by this function,
most swear at it, when Microsoft automatically inserts it in any formula outside
of a pivot table that points inside the pivot table. To enter a formula without
getting GETPIVOTDATA, type the cell references rather than using the mouse
or arrow keys. To turn it off permanently in Excel 2007, go to PivotTable Tools
Options. Open the Options dropdown and uncheck Generate GetPivotData. In
Excel 2003, there is an icon available, but you have to add it to a toolbar. Use
Tools, Customize. Go to the Commands tab. On the left side choose Data. On
the right side, scroll almost to the bottom. Drag the Generate GetPivotData icon
onto any toolbar (even the Pivot Table Toolbar). Click the icon to turn off the
GROWTH(known_y’s,known_x’s,new_x’s,const) [Category: Statistical]]
Calculates predicted exponential growth by using existing data. GROWTH
returns the y-values for a series of new x-values that you specify by using existing
x-values and y-values. You can also use the GROWTH worksheet function to ﬁ t
an exponential curve to existing x-values and y-values.
HARMEAN(number1,number2, ...) [Category: Statistical]]
Returns the harmonic mean of a data set. The harmonic mean is the reciprocal
of the arithmetic mean of reciprocals.
HEX2BIN(number,places) [Category: Engineering]*
Converts a hexadecimal number to binary.
HEX2DEC(number) [Category: Engineering]*
Converts a hexadecimal number to decimal. Guru Tip: Useful for converting
Hex color codes from a web page into RGB values. See DEC2HEX for an
HEX2OCT(number,places) [Category: Engineering]*
Converts a hexadecimal number to octal.
[Category: Lookup & Reference]
Searches for a value in the top row of a table or an array of values, and then
returns a value in the same column from a row you specify in the table or array.
Use HLOOKUP when your comparison values are located in a row across the
top of a table of data, and you want to look down a speciﬁ ed number of rows.
Use VLOOKUP when your comparison values are located in a column to the left
of the data you want to ﬁ nd. Guru Tip: If you can use VLOOKUP, you know how
to use HLOOKUP. If you know how to use VLOOKUP and also Paste Special
Transpose, then you never have to use HLOOKUP!