Happy Holi - 2013

Wishing You and your family a very Happy & Safe Holi.

This occasion brings people from all walks of life together, essentially celebrating the victory of good over evil. 

In the earlier days, gulal or the pink colour, was used in large quantities. Today, apart from these, colours like green, red, yellow and purple have made their way. Hostile colours like silver, black and golden are also being used.

Synthetic colours contain harmful chemicals that cause damage to skin and eyes. 

Age Calculator

Dear Friends.

We all love automated results.. A simple formulas can make our work so easy with full of accuracy. 

Today I am going to tell you about a formula which you will not find in the inbuilt dictionary of MS Excel. This formula is simple and useful in cases when you want to know the correct age of a person in Years, Months & Days.

  

Birth date :	23-Jan-84
Years lived :	29	=DATEDIF(C8,TODAY(),"y")
and the months :	1	=DATEDIF(C8,TODAY(),"ym")
and the days :	30	=DATEDIF(C8,TODAY(),"md")

I have attached a file for your reference and download.

Click here to download the excel file.

The Excel Formula : SUMIF

Dear Friends.

We sometimes required to sum the value in the given range with certain criteria. It is very painful and time taking tasks if you have to do this manually, specially in a large database. So, here you will find the ways to get the output within seconds using the Excel inbuilt formulas.

		ColumnC	ColumnD	ColumnE
	Row3	Item	Date	Cost
	Row4	Brakes	01-Jan-98	80
	Row5	Tyres	10-May-98	25
	Row6	Brakes	01-Feb-98	80
	Row7	Service	01-Mar-98	150
	Row8	Service	05-Jan-98	300
	Row9	Window	01-Jun-98	50
	Row10	Tyres	01-Apr-98	200
	Row11	Tyres	01-Mar-98	100
	Row12	Clutch	01-May-98	250
	Total cost of all Brakes bought.				160	=SUMIF(C4:C12,"Brakes",E4:E12)
	Total cost of all Tyres bought.				325	=SUMIF(C4:C12,"Tyres",E4:E12)
	Total of items costing £100 or above.				1000	=SUMIF(E4:E12,">=100")
	Total of item typed in following cell.			service	450	=SUMIF(C4:C12,E18,E4:E12)
	What Does It Do ?
	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.

For any confusion, feel free to comment or mail at sharma.ctc@gmail.com

The Excel Formula : MAX

Dear Friends.
We sometimes required to find the maximum value in in the given range . Its very painful and time taking tasks if you have to do this manually, specially in a large database. So, here you will find the ways to get the output within seconds using the Excel inbuilt formulas.

Values								Maximum
120		800	100		120	250		800		=MAX(C4:G4)
Dates								Maximum
01-Jan-98		25-Dec-98	31-Mar-98		27-Dec-98	04-Jul-98			27-Dec-98	=MAX(C7:G7)
What Does It Do ?
This function picks the highest value from a list of data.
Syntax
=MAX(Range1,Range2,Range3... through to Range30)
Formatting
No special formatting is needed.
Example
In the following example the =MAX() function has been used to find the highest value for
each region, month and overall.
Sales	Jan			Feb	Mar			Region Max
North	£5,000			£6,000	£4,500			£6,000			=MAX(C23:E23)
South	£5,800			£7,000	£3,000			£7,000
East	£3,500			£2,000	£10,000			£10,000
West	£12,000			£4,000	£6,000			£12,000
Month Max	£12,000			£7,000	£10,000
					=MAX(E23:E26)
Overall Max	£12,000
	=MAX(C23:E26)