Friday, September 11, 2015

How to use excel formula to get dynamic results

Here, we will see how we can get dynamic output with a simple function.

For example, assume that you have an Excel sheet having some 2000 web domain names ending with forward slash "\"  

How will you remove that forward slash "\" from the list?
Using LEFT command, we can get the result.


text = > Select the cell data
num_chars  = > Number of characters you want to fetch


24 = Number of characters in the cell.

In =LEFT(A2,24) number 24 has to change based on cell length

By using another simple formula, LEN, you can find the number of characters in the cell.

Syntax=LEN(text) = It will tell us number of characters in a cell

Now, using both LEFT and LEN, we can get dynamic results.



Thursday, January 22, 2015

Count The Number Of Cells With Text In Excel

      If you need to count a text in Microsoft Excel, what we do?

      We know =count ( ) formula  to count the number of cells with Contain number.

      But count the cells with Contain TEXT?

          = COUNTA ( )
    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) 

    Example 1

      The following table show, how to count total no. of cell contain TEXT
                   =COUNTA( ) - COUNT( )


 Example 2
The following table was used by a school to keep track of the examinations taken by students
Each exam passed was graded as 1, 2 or 3.
A failure was entered as Fail.
The school needed to known how many students sat for each exam.
The school Also needed to know how many exams were taken by students .

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

Twitter Bird Gadget