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.


Syntax
=LEFT(text,[num_chars])

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

Domain

 =LEFT(A2,24)
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.







Result


  


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.





Saturday, March 1, 2014

How can we find the position of letter in Excel ?

FIND


What Does It Do?

This function looks for a specified letter inside another piece of text.
When the letter is found the position is shown as a number.
If the text contains more than one reference to the letter, the first occurrence is used.
An additional option can be used to start the search at a specific point in the text, thus
enabling the search to find duplicate occurrences of the letter.
If the letter is not found in the text, the result #VALUE is shown.

Syntax

 =FIND(LetterToLookFor,TextToLookInside,StartPosition)

LetterToLookFor : This needs to be a single character.
TextToLookInside : This is the piece of text to be searched through.
StartPosition : This is optional, it specifies at which point in the text the search should begin.

Formatting

No special formatting is needed, the result will be shown as a number.



Friday, February 28, 2014

How to use ISBLANK function in MS EXCEL?


ISBLANK

What Does It Do?
This function will determine if there is an entry in a particular cell.
It can be used when a  spreadsheet has blank cells which may cause errors, but which will be filled later as the data is received by the user.

Usually the function is used in conjunction with the =IF() function which can test the result of the = ISBLANK()


Syntax
     
 
 =ISBLANK(CellToTest)


Formatting
Used by itself the result will be shown as TRUE or FALSE.

Example
The following example shows a list of cheques received by a company.
When the cheque is cleared the date is entered.
Until the Cleared date is entered the Cleared column is blank.
While the Cleared column is blank the cheque will still be Outstanding.
When the Cleared date is entered the cheque will be shown as Banked.
The =ISBLANK() function is used to determine whether the Cleared column is empty or not.

Monday, May 13, 2013

HOW TO MAKE SYMBOLS WITH KEYBOARD


I would to share few important symbols using Keyboard.


Alt + 0153..... ™... trademark symbol

Alt + 0169.... ©.... copyright symbol

Alt + 0174..... ®....registered ­ trademark symbol

Alt + 0176 ...°......degre ­e symbol

Alt + 0177 ...±....plus-or ­-minus sign

Alt + 2 ...........bla­ck smiley face

Alt + 15..........su­n

Alt + 12...........female sign

Alt + 11...........m­ale sign

Alt + 6............spade

Alt + 5............. ­Club

Alt + 3.............. ­Heart

Alt + 4............. ­Diamond

Alt + 13...........e­ighth note

Alt + 14............ ­beamed eighth note

Alt + 8721.... ∑.... N-ary summation (auto sum)

Alt + 251.....√.....s­quare root check mark

Alt + 8236.....∞..... ­infinity

Alt + 24............ ­up arrow

Alt + 25............ ­down arrow

Alt + 26..........ri­ght arrow

Alt + 27...........l­eft arrow

Alt + 18...........u­p/down arrow

Alt + 29.........left right arrow


Wednesday, March 27, 2013

Excel Percentage Formula

Percentages


 There are no specific functions for calculating percentages.
 You have to use the skills you were taught in your maths class at school!
Finding a percentage of a value

    





Example 1
A company is about to give its staff a pay rise.




The wages department need to calculate the increases.



Staff on different grades get different pay rises.











Finding a percentage increase 
     
















Example 2







A company is about to give its staff a pay rise.




The wages department need to calculate the new salary including the % increase.


Staff on different grades get different pay rises.












Finding one value as percentage of another











You will need to format the result as % by using the % button



on the toolbar.
Example 3







An manager has been asked to submit budget requirements for next year.


The manger needs to specify what will be required each quarter.



The manager knows what has been spent by each region in the previous year.


By analysing the past years spending, the manager hopes to predict


what will need to be spent in the next year.








Finding an original value after an increase has been applied









Example 4







An employ has to submit an expenses claim for travelling and accommodation.


The claim needs to show the VAT tax portion of each receipt.



Unfortunately the receipts held by the employee only show the total amount.


The employee needs to split this total to show the original value and the VAT amount.











Thursday, April 19, 2012

How to Change the Default Font in Microsoft Excel

If we working with Microsoft Excel 2007 , How can we change default font in Microsoft Excel ?

Click Office Button

Click "Excel Options"

following window will open.
Select font and change also size.

NOTE   You must restart Microsoft Office Excel to begin using the new default font and font size. The new default font and font size are used only in new workbooks that you create after you restart Excel. Existing workbooks are not affected.




Twitter Bird Gadget