Getting more from google search

Getting more from google search

I use these three settings to get more search results, not targeted at what I may have looked at previously – you get a wider and more balanced set of results. A bigger list of organic search results on 1 page so as I bypass many of the paid ads I have more organic searches displayed. For me I find an increase to 20 is more than fast enough and saves me having to scroll many pages to make the changes simply click the cog on the right and select search settings. Select never show instant results, slide your slide scale to 20 and select do not use private results. Hopefully you will get more of what you expect and not what google wants you to see.

 

DATEDIF the Hiden Excel Function

DATEDIF the Hiden Excel Function
Your required to calculate the number of months between two dates? You’ll be needing the Hiden DATEDIF function. Try typing =DATEDIF into Excel and it will pretend it doesn’t recognise it – see how it’s not in the list of functions to the left unless you type it all in. Though a common feature used in Microsoft Access For some reason Microsoft don’t think the DATEDIF function is worthy of any documentation since Excel 2000. In fact they say they only include it in recent versions for backward compatibility. The DATEDIF function calculates the difference between two dates. DATEDIF syntax =DATEDIF(date1,date2,interval) Date1 is the start date , Date2 is the end date, Interval is the type of interval you want to calculate e.g. days, months, years. DATEDIF Interval Formats: ym = Complete calendar months between two dates as though the end date is in the same year as the start date. yd = Complete calendar days between two dates as though the end date is in the same year as the start date. y = years. Complete calendar years between two dates. md = complete calendar days between two dates as though the month and year of the end date is the same as the start date. m = months. Complete calendar months between two dates. d = days. Complete days between two dates. Let’s examine some DATEDIF examples: Note: you could achieve the same result for example 1 above with this formula: =C4-B4 Ok, so the above are some pretty basic calculations. Example 1: The number of Years since The Queen ascended the throne: =DATEDIF("6/2/1952",TODAY(),"Y") Value Returned= 61 Note: in the above formula TODAY() = 6th may 2013. The TODAY() function will return today’s date as per your computer clock. Also handy for report headers and the like. Example  2: The Queens age today (6th may 2013) in days, months and years: =DATEDIF("21/04/1926",TODAY(),"y")&" years,"&DATEDIF("21/04/1926",TODAY(),"ym") &" month(s), "&DATEDIF("21/04/1926",TODAY(),"md")&" Days" Value Returned=87 years,0 month(s), 15 Days The above formula uses the concatenation method (see Text Functions). Example 3: Calculate how long untill my next holiday. Supressing 0 values where there are no years or months returned: ="I Go On Holiday in "&IF(DATEDIF(TODAY(),"17/03/2014","y")=0,"",DATEDIF(TODAY(),"17/03/2013","y")&" years ")&IF(DATEDIF(TODAY(),"17/03/2014","ym")=0,"",DATEDIF(TODAY(),"17/03/2014","ym")&" months ")&DATEDIF(TODAY(),"17/03/2014","md")&" days" Value Returned= I Go On Holiday in 10 months 11 days the above formula will work as long as the date 17-03-14 is not exceeded We use the IF function to first evaluate whether there are any years to count, if not return nothing (as stipulated by the empty double quotes “”), and so on for months and days. Ideas for Using DATEDIF: Your age in years, months and days. Age is 51 Years, 7 Months and 5 Days And the formula ="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days" Using the layout above and changing the labels try out the ideas below. Length of service of an employee. Equipment age. Countdown to a date. DATEDIF Errors Returned If Date 1 is later than Date 2 Excel will return a #NUM error. If Date 1 or Date 2 is not a valid date you will get a #VALUE error. If the interval is not one of the above options Excel will return a #NUM error.

Excel EDATE

Excel EDATE Function Explained
To calculate due dates for projects or payments then Excel’s EDATE function will make life easier for you. Excel’s EDATE function is ideal for: calculating when a payment is due or project is to be completed calculating the date a scheduled number of months in the past or into the future Excel EDATE Function Syntax =EDATE(start_date,months) Returns the serial number of the date that is the indicated number of months before or after the start date. Calculate Date from Today 3 Months from today: =EDATE(NOW(),3) 3 Months prior to today: =EDATE(NOW(),-3) Note: if the date displays as a serial number simply change the formatting to a Date format. CTRL+1 to open formatting dialog box

The Excel Today function

The Excel Today function

 returns the current date. The function has no arguments and therefore, the syntax of the function is simply:

TODAY()

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

Today Function Examples

The following spreadsheets show 2 simple examples of calls to the Today function, which were made on 6th January 2011. The first example shows the function used alone, and the second example shows the function used as a part of a formula that calculates the number of days that have passed since 31-Dec-2009.
The spreadsheet on the left shows the format of the formulas and the spreadsheet on the right shows the results.

Formulas:   A B 1   =TODAY() 2 31-Dec-2009 =TODAY() - A2

Results:   A B 1   06-Jan-2011 2 31-Dec-2009 371

Today Function Problem

The following problem is encountered by some people when using the Excel Today function:

Common Problem

When you attempt to subtract another date from the result the Today function, (as in cell B2 of the example above), the result looks like a date (eg. "16/11/1900"), instead of returning an integer.

Possible Reason

This problem arises because the new cell or column is formatted as a 'date'. In this case, it is only the formatting of the cell that is wrong, NOT the value returned by the function.
To correct this:

• Highlight the cell(s) with the wrong formatting
• Right click with the mouse
• Select the Format Cells ... option and ensure the Number tab is selected
• Under the Category heading, select the option General and click OK

Microsoft Excel DMAX Function

Microsoft Excel DMAX Function

Basic Description

The Excel Dmax function finds the maximum value in a field (column) in a database for selected records only. The records to be included in the calculation are defined by a set of one or more user-specified criteria.

The syntax of the function is :

DMAX( database, field, criteria )

where the arguments are shown in the table below:

database	-	A range of cells containing the database. The top row of the database should specify the field names.
field	-	The field (column) within the database, that you want the maximum of. This can either be a field number, or can be the field name (ie. the header in the top row of the database) encased in quotes (eg. "Area", "Quarter", etc)
criteria	-	A range of cells that contain the criteria, to specify which records should be included in the calculation. The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below. eg. Quarter Area >1 North

Wildcards

You can also use the following wildcards in text-related criteria:

?    -    matches any single character
*    -    matches any sequence of characters

if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

The criteria supplied beneath each field heading can be either:

	-	a numeric value (including an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2011, FALSE)
or
	-	a text string (eg. "North", "Friday")
or
	-	an expression (eg. ">5", "<>0")

Note that the Excel database functions are not case sensitive. So, for example, the criteria ="North" will be satisfied by cells containing the text "North" or "north".

Excel Dmax Function Examples

	A	B	C	D
1	Quarter	Area	Sales Rep.	Sales
2	1	North	Jeff	$223,000
3	1	North	Chris	$125,000
4	1	South	Carol	$456,000
5	1	South	Tina	$289,000
6	2	North	Jeff	$322,000
7	2	North	Chris	$340,000
8	2	South	Carol	$198,000
9	2	South	Tina	$222,000
10	3	North	Jeff	$310,000
11	3	North	Chris	$250,000
12	3	South	Carol	$460,000
13	3	South	Tina	$395,000
14	4	North	Jeff	$261,000
15	4	North	Chris	$389,000
16	4	South	Carol	$305,000
17	4	South	Tina	$188,000

The following examples are based on the simple database on the right, which stores the sales figures for four sales representatives, over the four quarters of a year.

Example 1

If, from the example database, we wanted to find the highest sales figure in the North area, during quarter 2, we could use the Dmax function as shown below.

In the example, the criteria are specified in cells F1 - G2 and the Dmax formula is shown in cell F3:

	F	G
1	Quarter	Area
2	2	North
3	=DMAX( A1:D17, "Sales", F1:G2 )

The above Dmax function calculates the maximum of the values in cells D6 & D7, and therefore returns the value $340,000

Note that, in the above function, instead of typing in "Sales" for the field argument, we could have simply used the number 4 (to denote the 4th column of the database).

Example 2

In the example below, the Dmax function is used to find the highest quarterly sales figure for sales rep "Carol", in quarters 2, 3 or 4.

Again, the criteria are specified in cells F1 - G2 and the Dmax formula is shown in cell F3:

	F	G
1	Sales Rep.	Quarter
2	Carol	>1
3	=DMAX( A1:D17, "Sales", F1:G2 )

The above Dmax function finds the maximum of the values in cells D8, D12 and D16, and so returns the value $460,000

The Excel Dproduct function

The Excel Dproduct function

 calculates the product of a field (column) in a database for selected records, that satisfy user-specified criteria.
The syntax of the function is :

DPRODUCT( database, field, criteria )

where the arguments are shown in the table below:

database	-	A range of cells containing the database. The top row of the database should specify the field names.
field	-	The field (column) within the database, that you want to calculate the product of. This can either be a field number, or can be the field name (ie. the header in the top row of the database) encased in quotes (eg. "Date", "Type", etc)
criteria	-	A range of cells that contain the criteria, to specify which records should be included in the calculation. The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below. eg. Name Date Tom >01/01/2011

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

Wildcards
You can also use the following wildcards in text-related criteria:
?    -    matches any single character
*    -    matches any sequence of characters
if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.
eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

The criteria supplied beneath each field heading can be either:

	-	a numeric value (including an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2011, FALSE)
or
	-	a text string (eg. "Text", "Monday")
or
	-	an expression (eg. ">12", "<>0")

Note that the Excel database functions are not case sensitive. So, for example, the criteria ="Tuesday" will be satisfied by cells containing the text "Tuesday" or "tuesday".

Excel Dproduct Function Examples

	A	B	C	D
1	Name	Date	Test	Score
2	Gary	01-Jan-2011	Test1	4
3	Gary	01-Jan-2011	Test2	4
4	Gary	01-Jan-2011	Test3	3
5	Gary	05-Jan-2011	Test1	3
6	Gary	05-Jan-2011	Test2	4
7	Gary	05-Jan-2011	Test3	3
8	Kev	02-Jan-2011	Test1	2
9	Kev	02-Jan-2011	Test2	3
10	Kev	02-Jan-2011	Test3	5
11	Kev	05-Jan-2011	Test1	3
12	Kev	05-Jan-2011	Test2	2
13	Kev	05-Jan-2011	Test3	5

The following examples are based on the simple database on the right, which stores the ratings, across three tests, taken by two men on different dates.

Example 1

In the example below, the Dproduct function is used to calculate the product of Gary's scores on all three tests on 05-Jan-2010. The criteria are specified in cells F1 - G2 and the Dproduct formula is shown in cell F3.

	F	G
1	Name	Date
2	Gary	05-Jan-2011
3	=DPRODUCT( A1:D13, "Score", F1:G2 )

The above Dproduct function calculates the product of the scores in cells D5, D6 & D7, and therefore returns the value 36.

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