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

annoying feature.

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

example.

HEX2OCT(number,places) [Category: Engineering]*

Converts a hexadecimal number to octal.

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

[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!