Use of CONCAENATE in Excel

	Name 1	Name 2	Concatenated Text	Formula
	Alan	Jones	AlanJones	=CONCATENATE(C3,D3)
	Bob	Williams	BobWilliams	=CONCATENATE(C4,D4)
	Carol	Davies	CarolDavies	=CONCATENATE(C5,D5)
	Alan	Jones	Alan Jones	=CONCATENATE(C6," ",D6)
	Bob	Williams	Williams, Bob	=CONCATENATE(D7,", ",C7)
	Carol	Davies	Davies, Carol	=CONCATENATE(D8,", ",C8)
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.
Note
You can achieve the same result by using the & operator.
	Name 1	Name 2	Concatenated Text	Formula
	Alan	Jones	AlanJones	=C24&D24
	Bob	Williams	BobWilliams	=C25&D25
	Carol	Davies	CarolDavies	=C26&D26
	Alan	Jones	Alan Jones	=C27&" "&D27
	Bob	Williams	Williams, Bob	=D28&", "&C28
	Carol	Davies	Davies, Carol	=D29&", "&C29

CHAR() IN EXCEL

			ANSI Number	Character
			65	A				=CHAR(G4)
			66	B				=CHAR(G5)
			169	©				=CHAR(G6)

This function converts a normal number to the character it represent in the ANSI

character set used by Windows.

Syntax

=CHAR(Number)

The Number must be between 1 and 255.

AutoSum Shortcut Key

Auto Sum Short Key

Instead of using the AutoSum button from the toolbar,

you can press Alt and = to achieve the same result.

	Jan	Feb	Mar	Total
North	10	50	90	150
South	20	60	100	180
East	30	70	200	300
West	40	80	300	420
Total	100	260	690	1050

Formula  Alt+=  Syntax   =SUM(D12:D15)

Convert Hours To Minutes in Excel

To Convert Hours to Minutes.

HOUR	MINUTE	FORMULA
1:00	60	=HOUR(A3)*60+MINUTE(A3)
1:02	62	=HOUR(A4)*60+MINUTE(A4)
1:04	64	=HOUR(A5)*60+MINUTE(A5)
1:06	66	=HOUR(A6)*60+MINUTE(A6)

    

TRICK :-To Convert Hours to Minutes.

HOUR	MINUTE	FORMULA
1:00	60	=A3*60*24
1:02	62	=A4*60*24
1:04	64	=A5*60*24
1:06	66	=A6*60*24

AGE CALCULATION

Birth date :

1-Jan-87

Date from which you want to Calculate:
YEARs 26Years    =DATEDIF(B1,TODAY(),"Y")   For YEARS

MONTH 0 Month   =DATEDIF(B1,TODAY(),"YM")  For MONTHS

DAYS 15 Days        =DATEDIF(B1,TODAY(),"MD")    For DAYS

Use of FLOOR in Excel

Number	Result	Formula
0.01	0	=FLOOR(A2,1)
1.27	1	=FLOOR(A3,1)
5.97	5	=FLOOR(A4,1)

MAGIC TRICKS of FLOOR() in Excel

Suppose in a cell have 12345
If you sum all the character 1+2+3+4+5
                                          =15
Again Add 1+5
                 =6

In FLOOR ()

CELL	RESULT	FORMULA
12345	6	=A13-FLOOR(A13,9)

Note:- If the sum equal to 9 result will show 0

USE OF COUNTIF IN EXCEL 2007

To Count

# Character Value or Numeric Value use Countif

COUNTIF(RANGE,CRITERIA) 

EXAMPLE:-

NAME	Result	Formula
KISHAN	2	=COUNTIF(A2:A10,A2)
RAJESH	1	=COUNTIF(A3:A11,A3)
RAJNEESH	1	=COUNTIF(A4:A12,A4)
RAM	3	=COUNTIF(A5:A13,A5)
SHYAM	1	=COUNTIF(A6:A14,A6)
YOGESH	1	=COUNTIF(A7:A15,A7)
KISHAN	1	=COUNTIF(A8:A16,A8)
RAM	2	=COUNTIF(A9:A17,A9)
RAM	1	=COUNTIF(A10:A18,A10)

Note : If you freeze the formula the result show a fixed number which is have in the range:-

NAME	Result	Formula
RAJESH	1	=COUNTIF($A$2:$A$10,A2)
RAJNEESH	1	=COUNTIF($A$2:$A$10,A3)
SHYAM	1	=COUNTIF($A$2:$A$10,A4)
YOGESH	1	=COUNTIF($A$2:$A$10,A5)
KISHAN	2	=COUNTIF($A$2:$A$10,A6)
KISHAN	2	=COUNTIF($A$2:$A$10,A7)
RAM	3	=COUNTIF($A$2:$A$10,A8)
RAM	3	=COUNTIF($A$2:$A$10,A9)
RAM	3	=COUNTIF($A$2:$A$10,A10)

Note:- If you want to find a particular character in a range example is given below:-

NAME	Result	Formula
RAJESH	3	=COUNTIF($A$2:$A$10,"RAM")
RAJNEESH	3	=COUNTIF($A$2:$A$10,"RAM")
SHYAM	3	=COUNTIF($A$2:$A$10,"RAM")
YOGESH	3	=COUNTIF($A$2:$A$10,"RAM")
KISHAN	3	=COUNTIF($A$2:$A$10,"RAM")
KISHAN	3	=COUNTIF($A$2:$A$10,"RAM")
RAM	3	=COUNTIF($A$2:$A$10,"RAM")
RAM	3	=COUNTIF($A$2:$A$10,"RAM")
RAM	3	=COUNTIF($A$2:$A$10,"RAM")