VBA, IF then condition

IF THEN: IF Then is use to perform comparison two values. However, to perform the test for set of data you will have to use IF then with Do while or For Next loop.

Lets take a look at basic IF Then example with single condition.

Sub LoopIFThen1()
If ActiveCell.Value >= 18 Then
    MsgBox "You are Major"
Else
    MsgBox "You are Minor"
End If
End Sub

If the active cell has value more than or equal to 18 then message box will appear saying "you are 18" otherwise nothing will happen.

Now, we will use IF then to see alternate condition as well. Alternate condition is like what should code perform if active cell is less than 18.

Sub LoopIFThen2()
If ActiveCell.Value >= 18 Then
    MsgBox "You are Major"
Else
    MsgBox "You are Minor"
End If
End Sub

After trying to simple example we will move little complex example with multiple condition. Like if the active cell has age entered than in next cell it will tell you whether you are infant, minor, major or senior citizen.

Sub LoopIFThen3()
If ActiveCell.Value < 2 And ActiveCell.Value <> "" Then
    ActiveCell.Offset(0, 1) = "Infant"
ElseIf ActiveCell.Value > 2 And ActiveCell.Value < 18 And ActiveCell.Value <> "" Then
    ActiveCell.Offset(0, 1) = "Minor"
ElseIf ActiveCell.Value >= 18 And ActiveCell.Value <= 60 And ActiveCell.Value <> "" Then
    ActiveCell.Offset(0, 1) = "Major"
ElseIf ActiveCell.Value > 60 And ActiveCell.Value <= 100 And ActiveCell.Value <> "" Then
    ActiveCell.Offset(0, 1) = "SENIOR CITIZEN"
Else
    MsgBox "Please check Value"
End If
End Sub

In Above example, Activecell(0,1) refers to next column in right. In forthcoming post, we will use other loops like Do while, For Next loop along with IF Then. Also, you if you have problem trying this example then try downloading the file with example. Link for same is mentioned below

Download IF Then Example

Please post your comments about the post and subscribe via email to receive latest updates

We assure you knowledge, not SPAM!

Read more on this article...

Shortcut to show all formula

At times I have seen people validating data and formula's on MS Excel sheet. It's very tedious if you go to each cell to view formula. Here is short cut to reveal all formulas. Trust me its very useful while auditing data in MS Excel

Shortcut key: Ctrl + `

Now if you have difficulty locating ` key. Let me help you, on most of standard keyboard `is located before 1 key or above tab key.

In last few months, I have posted lot of MS Excel Basic posts to ensure that everyone has something to learn who spare time and visit my blog.  As an advice I will say that learn one shortcut/formula in a day. Very soon you will be many other who do not follow this practice.

I would appreciate if you provide comments and subscribe via email to get latest email.

We assure you knowledge, not SPAM!

Read more on this article...

Countif function

This function works like SUMIF function. Like in last post I am using the same example. As the name suggest, COUNTIF finds the count based on criteria provided by user. Very useful when you are summarizing data or excluding unwanted  by setting criteria. Also, in forthcoming post I will provide examples of COUNTIF function that too in complex formula's. I will summarize data from baby product sales using Countif. 

Formula: =COUNTIF(RangeOfThingsToBeCounted ,CriteriaToBeMatched)

Here is example below which show day-wise baby product sales.

Date	Item	Quantity	Cost	Amount
17-Mar-09	Dolls	44	148	6512
12-Jan-09	Hairpin	8	57	456
15-Apr-08	Diapers	8	176	1408
28-Jun-08	Baby Oil	29	124	3596
24-Nov-09	Dolls	43	120	5160
16-Oct-09	Diapers	34	159	5406
24-Mar-09	Baby Oil	39	196	7644
16-Jan-09	Diapers	26	154	4004
17-Apr-08	Dolls	28	84	2352
4-Jul-08	Baby Oil	39	82	3198
28-Nov-09	Diapers	36	101	3636
20-Oct-09	Cap	30	117	3510
3-Apr-09	Dolls	17	122	2074
23-Jan-09	Cap	45	55	2475
25-Apr-08	Dolls	6	93	558
				51989

The task here is to find count of each product in summary table based on Item name. So item name is criteria here. 

Table below shows part one where we want to summarize the sales table item-wise.

Range part of COUNTIF covers B2:B16 examines which cells has item name, B22 has Doll which it takes as Criteria can only count where it finds respective items.

Items	Count	Formula
Dolls	5	=COUNTIF(B2:B16,B22)
Hairpin	1	=COUNTIF(B3:B17,B23)
Diapers	4	=COUNTIF(B4:B18,B24)
Baby Oil	3	=COUNTIF(B5:B19,B25)
Cap	2	=COUNTIF(B6:B20,B26)

Another examples below demonstrates find count of items where count if greater than 25.

Criteria	Total	Formula
Count where Quatity >25	11	=COUNTIF(C2:C16,">25")

Range part of COUNIF covers C2:C16 examines quantity column which cells has number greater than 25. Here Criteria is a string ">25".

I am sure this post is helpful in understanding COUNTIF function. Post your comments

I am sure after SUMIF in the last post, you will find COUNTIF easy. Enclosed is link to download the COUNTIF Example

COUNTIF Example download

If you are first time visitor, please subscribe via email to receive updates, add-ins, e-books and lot more.

We assure you knowledge, not SPAM!

Read more on this article...

SUMIF function to Summarize data

This function works like a simple Sum function. But you can add a criteria to it. It is very useful when you want to find out sum of values for certain items from the huge list of items. As we go ahead I will take you through the formula syntax and example. Also, if we use SUMIF and COUNTIF(which is another  similar function in MS Excel) together than we can calculate average based on criteria. In my next post I will describe COUNTIF.

Formula: =SUMIF(RangeOfThingsToBeExamined ,CriteriaToBeMatched ,RangeOfValuesToTotal)

Here is example below which show baby day-wise baby product sales.

Date	Item	Quantity	Cost	Amount
17-Mar-09	Dolls	44	148	6512
12-Jan-09	Hairpin	8	57	456
15-Apr-08	Diapers	8	176	1408
28-Jun-08	Baby Oil	29	124	3596
24-Nov-09	Dolls	43	120	5160
16-Oct-09	Diapers	34	159	5406
24-Mar-09	Baby Oil	39	196	7644
16-Jan-09	Diapers	26	154	4004
17-Apr-08	Dolls	28	84	2352
4-Jul-08	Baby Oil	39	82	3198
28-Nov-09	Diapers	36	101	3636
20-Oct-09	Cap	30	117	3510
3-Apr-09	Dolls	17	122	2074
23-Jan-09	Cap	45	55	2475
25-Apr-08	Dolls	6	93	558
				51989

The task here is to summarize report item-wise and bifurcate total in two part, part one where quantity is sold more than 25 and other part is where quantity is less or equal to 25. There are many ways to achieve this task. But here we will try SUMIF function to achieve it. Another way is PIVOT which I will soon post as another post.

Table below shows part one where we want to summarize the sales table item-wise.

Items	Amount total	Formula
Dolls	16656	=SUMIF(B2:B16,C19,E2:E16)
Hairpin	456	=SUMIF(B3:B17,C20,E3:E17)
Diapers	14454	=SUMIF(B4:B18,C21,E4:E18)
Baby Oil	14438	=SUMIF(B5:B19,C22,E5:E19)
Cap	5985	=SUMIF(B6:B20,C23,E6:E20)

Range part of SUMIF covers B2:B16 examines which cells has Item Dolls, B22 has Doll which it takes as Criteria, SumRange which is from E2:E16 add up all the respective cell in amount column which has "Dolls" in B column

Table below shows part two our task where we are summarize amount total where quantity greater than 25 or less than equal to 25

Criteria	Total	Formula
Total of Amount for Quatity >25	47493	=SUMIF(C2:C16,">25",E2:E16)
Total of Amount for Quatity <=25	4496	=SUMIF(C3:C17,"<=25",E3:E17)
	51989

Range part of SUMIF covers C2:C16 examines quantity column which cells has number greater than 25. Here Criteria is a string ">25", SumRange which is from E2:E16 add up all the respective cell where quantity is greater than 25.

I am sure this post is helpful in understanding SUMIF function. Post your comments

If you have any further doubt with SUMIF, please download example of SUMIF function or you can write me. I assure you prompt response.

SUMIF Example download

If you are first time visitor, please subscribe via email to receive updates, add-ins, e-books and lot more.

We assure you knowledge, not SPAM!

Read more on this article...

Days360 to find number of days

This function returns number of days between two dates. Only difference between Days360 and Datedif is Days360 consider year as 360 days and 12 months of 30 days each. Days360 is useful in accounting systems where year needs to be considered as 360 days.

Formula: DAYS360(StartDate, EndDate, TrueorFalse)

True: Enables the formula for European account systems.

False: Enables the formula for USA account systems.

Take a look at the example in the table below. Copy-Paste the table starting from A1

Start Date	End Date	Formula	Results
1-Jan-2009	7-Jan-2009	=DAYS360(A1,B1,True)	6
1-Jan-2009	1-Feb-2009	=DAYS360(A2,B2,True)	30
1-Jan-2009	31-Mar-2009	=DAYS360(A3,B3,True)	89
1-Jan-2009	1-Dec-2009	=DAYS360(A4,B4,True)	359

If you take a look at each row you will find that the formula does not include last day. Like 1-Jan-2009 and 7-Jan-2009 has 7 days between them. But formula returns 6. Hence, to correct this we will add 1 to formula.

=DAYS360(A1,B1,True) +1

Currently, I am posting basic functions of MS Excel. To receive updates on email, please enter your email in to Subscribe to Free MS Excel help.

We assure knowledge, no spam.

Read more on this article...

Auto Sum using shortcut

This method will reduce the time you take to write long sum formula. During my experience about using MS Excel, Sum is most common formula I used across most of MS Excel jobs. I prefer to either record a macro or find shortcut for the most repeated task. This not only saves time but also increase efficiency.

Shortcut key: Alt + =

Month	2008	2009	Total
January	2241	2214	Press Alt + = key here
February	2124	2356
March	2780	2476
Total	Press Alt + = key on this cell

When you press Alt + = key at the end of column you will get sum of column and Alt + = key at the end of row will give you sum of row as shown in example.

Note: This auto sum feature works only on the columns and rows where data is continuous without any empty cells in between.

Subscribe here, its free.

We assure you knowledge, not SPAM!

 

Read more on this article...

Vlookup function to find if value is present in range

I am little known for my MS Excel knowledge which I thought of sharing around. For beginners, Vlookup is biggest challenge and personally I have ample of people who ask me to teach Vlookup. Here is post to help all beginners with Vlookup formula, my all best wishes are with you. Approach will be in following manner. I will first explain you the formula syntax followed by example. To make best out of this post, I would suggest you to copy-paste the example in a MS Excel sheet. 

Formula: VLOOKUP(Valueyouwanttofind, Rangefromwhichyouwantofind, Columnnumber, SortedorUnsorted)

Valuesyouwanttofind are the customer id listed in column 1 of table below.

CustomerID	CustomerExistorNot	Result
50043	=VLOOKUP(A2,Sheet2!$A$1:$C$20,1,0)	50043
50048	=VLOOKUP(A3,Sheet2!$A$1:$C$20,1,0)	50048
40325	=VLOOKUP(A4,Sheet2!$A$1:$C$20,1,0)	#N/A

 Rangefromwhichyouwantofind are the customersid listed in column1 of table below. Since we are working to find out if the values existst or not hence we will choose columnnumber as 1 which will return you the customerid from table below and will return #N/A if the customer do not exist in table 2.

CustomerID	CustomerName	City
50041	Sharma K	Mumbai
50047	Chauhan M	Delhi
50048	Ritu K	Kanpur
40023	Khan L	Vashi
45601	Verma K	Belapur
50023	Areeb	Mumbra
40215	Riyaz A	Mumbai
41280	Dadan A	Kharghar
40326	Amit D	Belapur
50023	Faheem A	Mumbra
40215	Riyaz A	Mumbai
40351	Rodriquez P	Mumbai
42892	Raheem A	Kolkata
50043	Keith S	Mumbai
40052	Mahadevan S	Mumbai
40234	Ganguly D	Mumbai
40021	Smith M	Delhi
40020	Chakorborthy M	Vashi
40019	Raju C	Delhi

Copy-Paste both table in Sheet1 and Sheet2 starting from A1 cell.

SortedorUnsorted is the last parameter which can be true/false or 1/0. I would suggest you to use 0 if you are not sure whether the data is sorted(indexed) or not.

The most important things you should remember when you use Vlookup is Rangefromwhichyouwantofind should have Valuesyouwanttofind values listed as the left most columns. Like in the above scenario, you may have columns before customerid column but your range for Rangefromwhichyouwantofind should start with customerid. Also, use absolute reference in Rangefromwhichyouwantofind.

Do write us your view about this post. We will provide you something better that would help you understand better.  Enclosed below is link from Official Microsoft site explaining Vlookup function.

http://support.microsoft.com/kb/181213

Also, take a look at video tutorial design to help you with Vlookup.

 

If you are new/first time visitor, do subscribe via email to receive latest updates, e-books, free add-ins and lot more.

We assure you knowledge, not SPAM!

Read more on this article...

Find Absolute value using ABS function

In this post we will understand a very basic function ABS which returns value/magnitude. Like for ABS(-9) will return 9, ABS(10) will return 10 and so on. Table mentioned below is example which will help you in understanding the ABS function. Also, we will discuss one application of this formula. I would appreciate if you can spend some time write comment and do let us know if you have any issues. We are here to help.

Copy paste the table starting from A1 cell of your MS Excel sheet.

Number	Absolute Value	Formula
25	25	=ABS(A2)
-62	62	=ABS(A3)
-3.5	3.5	=ABS(A4)
3.5	3.5	=ABS(A5)

Formula:
=ABS(Address Of Cell)

Examples:
In the example below, we are describing a model of target-actual table. Fifth column shows the percentage of actual exceeded/shortfall. Table 1 will illustrate calculation without ABS function while table 2 will describe using ABS function.


Difference for 7-Mar-06 is negative as actual has exceeded target and hence the percentage appear as negative. No matter whether you have achieved or not but percentage cannot be negative.

Table 1

Date	Target	Actual	Difference	Exceeded/Shortfall Percentage
1-Mar-06	40	37	3	7.50%
4-Mar-06	54	54	0	0.00%
7-Mar-06	60	65	-5	-8.33%
			Target - Actual

Table 2

Date	Target	Actual	Difference	Exceeded/Shortfall Percentage
1-Mar-06	40	37	3	7.50%
4-Mar-06	54	54	0	0.00%
7-Mar-06	60	65	5	8.33%
			ABS(Target - Actual)

Kindly post your comments and subscribe to this blog via email to receive latest updates, excel tips, e-books and free add-ins. Click here to Subscribe

We assure you knowledge, not SPAM!

Read more on this article...