Tuesday, November 15, 2011

Excel Tips : Pre-select the range of cells to be entered

Pre-select the range of cells to be entered

You Know to use Tools - Options - Edit - Move Selection After Enter Direction - Right to force Excel to move to the right when you press the enter key. This is good when you have to enter data going across a row. It is particularly useful if you are entering numbers on the numeric keypad. The trick allows you to type 123 Enter and end up in the next cell. By keeping your hands on the numeric keypad, you can enter the numbers faster.
Someone suggested an improvement to this technique. Pre-select the range where you will be entering the data. The advantage is that when you get to the last column and press Enter, Excel will jump to the beginning of the next row. In the image below, pressing Enter will move you to cell B6. 



Tuesday, November 15, 2011

Excel Tips : Find the Difference Between Two Dates

 Find the Difference Between Two Dates


Excel help doesn't talk about this function, i is a great way to find the difference between two dates.
The syntax is =DATEDIF(EarlierDate,LaterDate,"Code")
Here are the valid values that you can use for Code.
  • Y - will tell you the number of complete years between the two dates.
  • YM - will tell you the number of complete months, excluding the years, between the two dates.
  • MD - will tell you the number of complete days, excluding complete months, between the two dates.
  • M - will tell you the number of complete months. For example, have been alive for 495 months
  • D - will tell you the number of days. For example, have been alive for 15,115 days. This is a trivial use, since you could just subtract one date from another and format as a number to duplicate this code.
Monday, November 14, 2011

Excel Function : SUM IF

Item Date Cost
Apple 01-Jan-11 300
Banana 10-May-11 50
Grape 01-Feb-98 200
Pumpking 01-Mar-98 150
Apple 05-Jan-98 40
Apple 01-Jun-98 100
Banana 01-Apr-98 350
Grape 01-Mar-98 100
Pumpking 01-May-98 250
Total of all Apples 440 = SUMIF(A2:A10,"Apple",C2:C10)
Total of all Banana 400 = SUMIF(A2:A10,"Banana",C2:C10)
Total of all Grape 300 = SUMIF(A2:A10,"Grape",C2:C10)
Total of all Pumpking 400 = SUMIF(A2:A10,"Pumpking",C2:C10)
SUM IF
This function adds the value of items which match criteria set by the user.
Syntax              
=SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)
=SUMIF(C4:C12,"Brakes",E4:E12) This examines the names of products in C4:C12.
It then identifies the entries for Brakes.
It then totals the respective figures in E4:E12
 =SUMIF(E4:E12,">=100") This examines the values in E4:E12.
If the value is >=100 the value is added to the total.
Formatting              
No special formatting is needed.
Saturday, November 12, 2011

Download Form 27A in Excel Format

Click here to Download Form 27A

Form 27 A : Each e-TDS return file should be accompanied by a duly filled and signed (by an authorised signatory) Form 27A in physical form.
There should be not any overwriting / striking on Form 27A. If there is any, then the same should be ratified by an authorised signatory.
The Control Chart in Form 27A is duly filled in all columns and verified and as enclosed in paper form with the e-TDS return on computer media.
The Control totals of the amount paid and the tax deducted at source as mentioned at item No.4 of Form No.27A tally with the corresponding totals in the e-TDS return in Form No. 24 or Form No. 26 or Form No.27, as the case may be   
Friday, November 11, 2011

Excel Function to Convert Rupees in Figure to Rupees in Word

Following function convert ( Rupees in figure ) numeric value in a Microsoft Excel worksheet cell into its equivalent in (Rupees in Word ) English words.
e.g.  =SpellNumber(2450) will display as Rupees Two Thousand Four Hundred Fifty Only

After copying following code, Enable macro and use above function.

How to create the sample function Called SpellNumber

  1. Start Microsoft Excel.
  2. Press ALT+F11 to start the Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. Type the following code into the module sheet.

Function SpellNumber(amt As Variant) As Variant
Dim FIGURE As Variant
Dim LENFIG As Integer
Dim i As Integer
Dim WORDs(19) As String
Dim tens(9) As String
WORDs(1) = "One"
WORDs(2) = "Two"
WORDs(3) = "Three"
WORDs(4) = "Four"
WORDs(5) = "Five"
WORDs(6) = "Six"
WORDs(7) = "Seven"
WORDs(8) = "Eight"
WORDs(9) = "Nine"
WORDs(10) = "Ten"
WORDs(11) = "Eleven"
WORDs(12) = "Twelve"
WORDs(13) = "Thirteen"
WORDs(14) = "Fourteen"
WORDs(15) = "Fifteen"
WORDs(16) = "Sixteen"
WORDs(17) = "Seventeen"
WORDs(18) = "Eighteen"
WORDs(19) = "Nineteen"
tens(2) = "Twenty"
tens(3) = "Thirty"
tens(4) = "Fourty"
tens(5) = "Fifty"
tens(6) = "Sixty"
tens(7) = "Seventy"
tens(8) = "Eighty"
tens(9) = "Ninety"
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
FIGLEN = Len(FIGURE)
If FIGLEN < 12 Then
FIGURE = Space(12 - FIGLEN) & FIGURE
End If
If Val(Left(FIGURE, 9)) > 1 Then
SpellNumber = "Rupees "
ElseIf Val(Left(FIGURE, 9)) = 1 Then
SpellNumber = "Rupee "
End If
For i = 1 To 3
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Crore "
ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Lakh "
ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Thousand "
End If
FIGURE = Mid(FIGURE, 3)
Next i
If Val(Left(FIGURE, 1)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 1))) + " Hundred "
End If
FIGURE = Mid(FIGURE, 2)
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
FIGURE = Mid(FIGURE, 4)
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & " Paise "
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & " Only "
End If
End Function
Tuesday, November 08, 2011

RANK FORMULA IN EXCEL



Values
Ranking Position
High to Low
7
4
 =RANK(C4,C4:C8)
4
5
 =RANK(C5,C4:C8)
25
1
 =RANK(C6,C4:C8)
8
3
 =RANK(C7,C4:C8)
16
2
 =RANK(C8,C4:C8)
Values
Ranking Position
Low to High
7
2
 =RANK(C11,C11:C15,1)
4
1
 =RANK(C12,C11:C15,1)
25
5
 =RANK(C13,C11:C15,1)
8
3
 =RANK(C14,C11:C15,1)
16
4
 =RANK(C15,C11:C15,1)
Values
Ranking Position
High to Low
10
5
 =RANK(C18,C18:C22)
30
2
 =RANK(C19,C18:C22)
20
4
 =RANK(C20,C18:C22)
30
2
 =RANK(C21,C18:C22)
40
1
 =RANK(C22,C18:C22)
What Does It Do ?










This function calculates the position of a value in a list relative to the other values in the list.
A typical usage would be to rank the times of athletes in a race to find the winner.
The ranking can be done on an ascending (low to high) or descending (high to low) basis.
If there are duplicate values in the list, they will be assigned the same rank. Subsequent ranks
would not follow on sequentially, but would take into account the fact that there were duplicates.
If the numbers 30, 20, 20 and 10 were ranked, 30 is ranked as 1, both 20's are ranked as 2, and
the 10 would be ranked as 4.
Value
Rank
30
1
 =RANK(B34,B34:B37)
20
2
 =RANK(B35,B34:B37)
20
2
 =RANK(B36,B34:B37)
10
4
 =RANK(B37,B34:B37)
Syntax









=RANK(NumberToRank,ListOfNumbers,RankOrder)
The RankOrder can be 0 zero or 1.
Using 0 will rank larger numbers at the top. (This is optional, leaving it out has the same effect).
Using 1 will rank small numbers at the top.
Formatting








No special formatting is needed.
Example









The following table was used to record the times for athletes competing in a race.
The =RANK() function was then used to find their race positions based upon the finishing times.
Athlete
Time
Race Position
Abhay
01:30
4
 =RANK(C53,C53:C58,1)
Karan
01:45
6
 =RANK(C54,C53:C58,1)
Sunil
01:02
1
 =RANK(C55,C53:C58,1)
Iqbal
01:36
5
 =RANK(C56,C53:C58,1)
Dev
01:27
3
 =RANK(C57,C53:C58,1)
Arjun
01:03
2
 =RANK(C58,C53:C58,1)