Youtube Android,Blogging SEO & Whatsapp Tricks

youtube SEO, tricks for blogger, wordpress tricks for earning money online youtube earning, whatsapp,windows, android & all about new technology.

Showing posts with label Excel Formulas. Show all posts

Excel Date Function


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.
  • Countif Function in MS Excel


    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.

    Countblank Formula in Excel For Count Blank Cell


    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.

    Excel Convert Function Formula with Examples


    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.

    MS Excel Counta Function


    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.

    Excel Count Function


    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.

    Excel Correl Function


    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.

    Excel Concatenate Formula Example


    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.
  • Excel Code Function ANSI Value


    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 with Example


    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.