The Excel PRODUCT function

The Excel PRODUCT function

 returns the product (multiplication) of a supplied set of numerical values.
The syntax of the function is :

PRODUCT( number1, [number2], ... )

where the number arguments are a set of numbers (or arrays of numbers) that you want to find the product of. These can be supplied to the function either directly, as values returned from other functions, or as references to cells containing numeric values.
In Excel 2007 or 2010, you can enter up to 255 number arguments to the Excel Product function, but in Excel 2003, the function can only accept up to 30 arguments.

for further details see: http://www.exceltraining-london.co.uk/excelFunctions/ExcelLondon/excelfunctiondictionary.html

Which Values are Included in the Excel Product Function Calculation?

Numbers and dates are always counted as numeric values by the Excel Product function. However, text representations and logical values are handled differently, depending on whether they are values stored in the cells of your spreadsheet, or they are supplied directly to the function.
The table below shows which values are included in the Excel Product Function calculation, and which values are ignored or produce errors:

	Value Within a Range of Cells	Value Supplied Directly to Function
Numbers	Included	Included
Dates	Included	Included
Logical Values	Ignored	Included (True=1; False=0)
Text Representations of Numbers & Dates	Ignored	Included
Other Text	Ignored	#VALUE! Error
Errors	Error	Error

Excel Product Function Examples

The following spreadsheet shows four different ways to input five numbers into the Excel Product function. The example illustrates how the number arguments can be input as either:

- individual numbers
- number arrays
- individual cells
- cell ranges

... or a combination of all of these.

Formulas:   A B 1 3 =PRODUCT( 3, 6, 2, 8, 5 ) 2 6 =PRODUCT( A1:A5 ) 3 2 =PRODUCT( 3, 6, 2, A4, A5 ) 4 8 =PRODUCT( {3,6}, A3:A5 ) 5 5

Results:   A B 1 3 1440 2 6 1440 3 2 1440 4 8 1440 5 5

The Excel GETPIVOTDATA function

The Excel GETPIVOTDATA function

 extracts data from an Excel Pivot Table
The format of the function is :

GETPIVOTDATA( data_field, pivot_table, [field1], [item1], [field2], [item2], ...)

goto http://www.exceltraining-london.co.uk/excelFunctions/ExcelLondon/excelfunctiondictionary.html for full listings

where the arguments are as follows:

data_field	-	The pivot table data field (ie. the value(s) in the center of the table) that you want to retrieve
pivot_table	-	A reference to a range of cells within a Pivot Table (used to specify the pivot table to be searched)
[field1], [item1], [field2], [item2], etc	-	Up to 126 optional pairs of fields and item names, (ie. The rows and columns headers and individual categories) for which you want the returned value. Note: The item values should be entered as follows: -    Numbers can be entered directly -    Dates should be entered as date serial numbers or by using the date function -    Times should be entered as decimals or by using the time function -    Text values should be entered in quotations

Note that, if the requested fields are not visible in the specified Pivot Table, the Getpivotdata function returns the #REF! error.
The easiest way to input the Getpivotdata function is simply to type "=" into a cell and then click on the Pivot Table value that you want to return. Excel automatically inserts the Getpivotdata function into the active cell.

Getpivotdata Function Examples

The examples below all refer to the following Pivot Table, which is located in columns A - G of the current Excel Worksheet.

	A	B	C	D	E	F	G
1
2	Sum of Invoice Amount Total		Item Type
3	Date	Salesperson	Digital TV	DVD Player	Ipod	Computer	Grand Total
4	Jan	John	$68,600	$13,800	$6,840	$69,600	$158,840
5		Kevin	$64,400	$7,800	$12,780	$36,800	$121,780
6		Pete	$46,200	$2,400	$2,700	$25,600	$76,900
7	Jan Total		$179,200	$24,000	$22,320	$132,000	$357,520
8	Feb	John	$68,600	$8,400	$9,720	$52,800	$139,520
9		Kevin	$61,600	$4,500	$7,920	$43,200	$117,220
10		Pete	$29,400	$3,900	$6,300	$33,600	$73,200
11	Feb Total		$159,600	$16,800	$23,940	$129,600	$329,940
12	Mar	John	$71,400	$9,300	$7,560	$80,000	$168,260
13		Kevin	$70,000	$10,200	$13,680	$58,400	$152,280
14		Pete	$43,400	$5,400	$3,240	$40,800	$92,840
15	Mar Total		$184,800	$24,900	$24,480	$179,200	$413,380
16	Grand Total		$523,600	$65,700	$70,740	$440,800	$1,100,840
17

=GETPIVOTDATA( "Invoice Amount", $A$2, "Date", "Jan" )
- Returns the value $357,520, which is the value of the Invoice Amount Total for all of the Date field, "Jan".


=GETPIVOTDATA( "Invoice Amount", $A$2, "Date", "Feb", "Item Details", "IPod" )
- Returns the value $23,940, which is the value of the Invoice Amount Total for the Date field, "Feb" and the Item Details field, "IPod".


=GETPIVOTDATA( "Invoice Amount", $A$2, "Date", "Feb", "Item Details", "IPod", "Salesperson", "Kevin" )
- Returns the value $7,920, which is the value of the Invoice Amount Total for the Date field, "Feb", the Item Details field, "IPod", and the Salesperson field "Kevin".


=GETPIVOTDATA( "Invoice Amount", $A$2, "Item Details", "IPod", "Salesperson", "Kevin" )
- Returns the Excel #REF! error as the Pivot Table doesn't show the totals for the Salesperson "Kevin".

The Excel VLOOKUP function

The Excel VLOOKUP function

 'looks up' a given value in the left-hand column of a data array (or table), and returns the corresponding value from another column of the array.
The format of the function is:

VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )

where the arguments are as follows:

lookup_value	-	The value that you want to look for, in the left-hand column of the supplied data array
table_array	-	The data array or table, that you want to search the left hand column of, for the supplied lookup_value
col_index_num	-	The column number, within the supplied array, that you want the corresponding value to be returned from
[range_lookup]	-	An optional logical argument, which can be set to TRUE or FALSE, meaning : TRUE - If the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value (Note: If range_lookup is set to TRUE, the left-hand column of the table_array must be in ascending order) FALSE - if the function cannot find an exact match to the supplied lookup_value, it should return an error

Vlookup Examples

Vlookup Example 1

In the spreadsheet below, columns A and B list an inventory of grocery items, and their prices, and cell E2 of the spreadsheet shows a simple example of the Vlookup function being used to look up the price of an item from the inventory.

	A	B	C	D	E
1	Item Description	Cost ($)		Current Item:	Current Item Cost ($)
2	Tinned Tomatoes	$0.90		Cornflakes	=VLOOKUP( D2, A:B, 2, FALSE )
3	Tinned Tuna	$1.50
4	Cornflakes	$3.50
5	Shortcake Biscuits	$1.00
6	Toothpaste	$4.10
7	Tinned Baked Beans	$0.99
8	White Sliced Bread	$0.80
9	. . .	.   .   .

The above Vlookup function returns the price for "Cornflakes", which is $3.50.
In this example:

-	the lookup_value is the text string "Cornflakes", which is located in cell D2
-	the table_array is columns A-B of the spreadsheet
-	the col_index_num is set to 2, to denote that the value returned should be taken from column 2 of the table_array
-	the range_lookup argument is set to FALSE, to indicate that we only want a result to be returned if an exact match to the lookup_value is found

The If Function Excel

The If Function Excel

you may have been issued with a excel function dictionary with one hundred and fifty functions


Our new online version contains almost 500 functions described with examples make this the first place to go get help for excel

http://www.exceltraining-london.co.uk/excelFunctions/ExcelLondon/excelfunctiondictionary.html

other information can be found by clicking on the icons at the bottom of the page.

Here's an example of what you may find. we call this the Vulcan function for all you trekie fans "it's logic captain"

Basic Description

The Excel IF function tests a user-defined condition and returns one result if the condition is true, and another result if the condition is false.
The syntax of the function is :

IF( logical_test, value_if_true, value_if_false )

where the arguments are as follows:

logical_test	-	The user-defined condition that is to be tested and evaluated as either TRUE or FALSE
value_if_true	-	The result that is to be returned from the function if the supplied logical_test evaluates to TRUE
value_if_false	-	The result that is to be returned from the function if the supplied logical_test evaluates to FALSE

Nesting the Excel If Function

The If function is frequently 'nested' in Excel. I.e. the value_if_true or the value_if_false argument is replaced with another call to the If function (see Example 3 below).
Excel 2003 allows up to 7 levels of nested If functions, but Excel 2007 and Excel 2010 allow up to 64 levels of nesting. For Example, the following formula (which has 8 levels of nesting), will result in an error in Excel 2003 but will work correctly in Excel 2007 or Excel 2010 :

=IF(A1=1,"red", IF(A1=2,"blue", IF(A1=3,"green", IF(A1=4,"brown", IF(A1=5,"purple", IF(A1=6,"orange", IF(A1=7,"yellow", IF(A1=8,"grey", IF(A1=9,"pink", "black" ) ) ) ) ) ) ) ) )

If you do find yourself using multiple levels of nesting, you should probably consider other Excel functions that can be used to obtain the same result more succinctly. For example, the above function could be made much simpler by using the Excel Choose function.

Excel If Function Examples

If Function Example 1

The following example shows the Excel If function applied to two sets of numbers. In this example, the logical_test checks whether the corresponding value in column B is equal to 0, and the function returns :

• The text string "div by zero" if the value in column B is equal to 0

or

• The value in column A divided by the value in column B if the value in column B is not equal to zero

	A	B	C	D
1	5	4	=IF( B1=0, "div by zero", A1/B1 )	- returns the value 1.25
2	5	0	=IF( B2=0, "div by zero", A2/B2 )	- returns the text string "div by zero"

The Excel Iferror

The Excel Iferror function

 tests if an initial supplied value (or expression) returns an error, and if so, returns a second supplied argument; Otherwise the function returns the initial tested value.
The Iferror function is new to Excel 2007, so is not available in earlier versions of MS Excel
The syntax of the function is:

IFERROR( value, value_if_error )

Where the arguments are as follows :

For a full range of functions see http://www.exceltraining-london.co.uk/excelFunctions/ExcelLondon/excelfunctiondictionary.html

value	-	The initial value or expression that should be tested
value_if_error	-	Value or expression to be returned if the initial value argument returns an error.

Improvement Compared to Excel 2003

The Excel Iferror function was introduced in Excel 2007.
Previously, in Excel 2003, many users of the Excel Vlookup function would combine this with the If function and the Iserror function, to test for an error, and return an appropriate result. This is shown in the following formula:

IF( ISERROR( VLOOKUP( ... ) ), "not found", VLOOKUP( ... ) )

the above formula checks if the Vlookup function returns an error, and if so, returns the text "not found". Otherwise the value returned by the Vlookup is used.
Although this formula is long and inefficient (as it requires 2 seperate calls to the Vlookup function), it is useful because it helps to keep your spreadsheet cells tidy and free from error messages.
In Excel 2007 or 2010, the above action can be performed much more efficiently and neatly, by using the Iferror function. The new formula is written as:

IFERROR( VLOOKUP( ... ), "not found" )

This is illustrated in the example below.

Iferror Function Examples

The following spreadsheet shows two examples of the Excel Iferror function. The formulas are shown in the top spreadsheet and the results are shown in the spreadsheet below.

Formulas:   A B C 1 Beth Class 1 =IFERROR( VLOOKUP( "Jim", A1:B5, 2 FALSE ), "not found" ) 2 Bob Class 2 =IFERROR( VLOOKUP( "Mary", A1:B5, 2 FALSE ), "not found" ) 3 Alf Class 2   4 Jim Class 3   5 Ann Class 3

Results:   A B C 1 Beth Class 1 Class 3 2 Bob Class 2 not found 3 Alf Class 2   4 Jim Class 3   5 Ann Class 3

Course Offer

Save 50% with the Buddy Scheme!

Book a place on one of our open courses and bring a colleague along for half price

That's right, book any one of our open courses (it doesn't matter which subject or level) and you can bring a colleague along with you for half the regular price!
Alternatively, you can use the second place for yourself or a colleague for one of our regular Excel public courses (Introduction, Intermediate or Advanced) at a later date.
Interested? Call now on 020 7920 9500 for full details

Air play not working - Apple TV stops allowing you to accept ipad airplay since update

have you had the problem you can mirror your I pad but can no longer stream, probably after doing updates, the answer go to your apple TV and turn on room conferencing and if like magic the ipad streams once again. hope you find this of use   It worked for me