Youtube Android,Blogging SEO & Whatsapp Tricks

What Does It Do?
This function creates a real date by using three normal numbers typed into separate cells.

Syntax

=DATE(year,month,day)

Formatting

The result will normally be displayed in the dd/mm/yy format.

By using the Format,Cells,Number,Date command the format can be changed.

What Does It Do ?
This function counts the number of items which match criteria set by the user.

Syntax

=COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)

The criteria can be typed in any of the following ways.

To match a specific number type the number, such as =COUNTIF(A1:A5,100)

To match a piece of text type the text in quotes, such as =COUNTIF(A1:A5,"Hello")

To match using operators surround the expression with quotes, such as =COUNTIF(A1:A5,">100")

Formatting

No special formatting is needed.

What Does It Do ?
This function counts the number of blank cells in a range.

Syntax

=COUNTBLANK(RangeToTest)

Formatting

No special formatting is needed.

Example

The following table was used by a company which was balloting its workers on whether the company should have a no smoking policy.
Each of the departments in the various factories were questioned.
The response to the question could be Y or N.
As the results of the vote were collated they were entered in to the table.

The =COUNTBLANK() function has been used to calculate the number of departments which
have no yet registered a vote.

What Does It Do ?

This function converts a value measure in one type of unit, to the same value expressed in a different type of unit, such as Inches to Centimetres.

Syntax

=CONVERT(AmountToConvert,UnitToConvertFrom,UnitToConvertTo)

Formatting

No special formatting is needed.

Example

The following table was used by an Import / Exporting company to convert the weight and size of packages from old style UK measuring system to European system.

Abbreviations

This is a list of all the possible abbreviations which can be used to denote measuring systems.

These characters can be used as a prefix to access further units of measure.
Using "c" as a prefix to meters "m" will allow centimetres "cm" to be calculated.

What Does It Do ?
This function counts the number of numeric or text entries in a list.
It will ignore blanks.

Syntax

=COUNTA(Range1,Range2,Range3... through to Range30)

Formatting

No special formatting is needed.

Example

The following table was used by a school to keep track of the examinations taken by each pupil.
Each exam passed was graded as 1, 2 or 3.
A failure was entered as Fail.

The school needed to known how many pupils sat each exam.
The school also needed to know how many exams were taken by each pupil.

The =COUNTA() function has been used because of its ability to count text and numeric entries.

What Does It Do ?
This function counts the number of numeric entries in a list.
It will ignore blanks, text and errors.

Syntax

=COUNT(Range1,Range2,Range3... through to Range30)

Formatting

No special formatting is needed.

Example

The following table was used by a builders merchant to calculate the number of sales
for various products in each month.

What Does It Do ?  

This function examines two sets of data to determine the degree of relationship
between the two sets.
The result will be a decimal between 0 and 1.
The larger the result, the greater the correlation.

In Table 1 the Monthly temperature is compared against the Sales of air conditioning units.
The correlation shows that there is an 0.864 realtionship between the data.

In Table 2 the Cost of advertising has been compared to Sales.
It can be formatted as percentage % to show a more meaning full result.
The correlation shows that there is an 28% realtionship between the data.

Syntax  

=CORREL(Range1,Range2)

Formatting
The result will normally be shown in decimal format.

Combine excel sheet cells value by using Concatenate Function

What Does It Do?

This function joins separate pieces of text into one item.

Syntax

=CONCATENATE(Text1,Text2,Text3...Text30) Up to thirty pieces of text can be joined.

Formatting

No special formatting is needed, the result will be shown as normal text.

Note

You can achieve the same result by using the & operator.

What Does It Do?

This function shows the ANSI value of a single character, or the first character in a piece of text.

The ANSI character set is used by Windows to identify each keyboard character by using a unique number.

There are 255 characters in the ANSI set.

Syntax

=CODE(Text)

Formatting

No special formatting is needed, the result will be shown as a number between 1 and 255.

Example

See the example for FREQUENCY.

Excel CLEAN Function Removes Non-Printing Characters

What Does It Do?

This function removes any nonprintable characters from text.

These nonprinting characters are often found in data which has been imported

from other systems such as database imports from mainframes.

Syntax

=CLEAN(TextToBeCleaned)

Formatting

No special formatting is needed. The result will show as normal text.