Sponsored Link

There was an error in this gadget

Sponsored Link

Tuesday, March 31, 2009

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...

Monday, March 23, 2009

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...

Wednesday, March 18, 2009

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...

Tuesday, March 17, 2009

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...

Wednesday, March 11, 2009

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...

Monday, March 9, 2009

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...

Sunday, March 8, 2009

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...

Sunday, March 1, 2009

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...