Saturday, 31 March 2012

Excel Formulae

CONCATENATE :

What Does It Do?
This function joins separate pieces of text into one item.

Syntax
 =CONCATENATE(Text1,Text2,Text3...Text30)

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.


Name 1Name 2Concatenated Text
AlanJonesAlanJones =CONCATENATE(C4,D4)
BobWilliamsBobWilliams =CONCATENATE(C5,D5)
CarolDaviesCarolDavies =CONCATENATE(C6,D6)
AlanJonesAlan Jones =CONCATENATE(C7," ",D7)
BobWilliamsWilliams, Bob =CONCATENATE(D8,", ",C8)
CarolDaviesDavies, Carol =CONCATENATE(D9,", ",C9)


COUNTIF:

What Does It Do ?
This function counts the number of items which match criteria set by the user.

Syntax
=COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)

Formatting
No special formatting is needed.



Item
Date
Cost





Brakes
01-Jan-98
80





Tyres
10-May-98
25





Brakes
01-Feb-98
80





Service
01-Mar-98
150





Service
05-Jan-98
300





Window
01-Jun-98
50





Tyres
01-Apr-98
200





Tyres
01-Mar-98
100





Clutch
01-May-98
250












How many Brake Shoes Have been bought.
2
=COUNTIF(C4:C12,"Brakes")
How many Tyres have been bought.
3
=COUNTIF(C4:C12,"Tyres")
How many items cost £100 or above.
5
=COUNTIF(E4:E12,">=100")








Type the name of the item to count.
service
2
=COUNTIF(C4:C12,E18)


No comments:

Post a Comment

Creative Commons Licence
BLOG by Suresh is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
Based on a work at http://ineedapple84.blogspot.in/.
Permissions beyond the scope of this license may be available at http://ineedapple84.blogspot.in/.