Excel Formulas and Functions A to Z with Examples

106
0

Today I’m going to show you Excel Formulas and Functions, You can Learn Start to End with Examples. Formulas are very important and helpful for mathematically, you can use them for a different purpose. There are a lot of Excel Formulas, but I have found Excel Formulas and Function useful and beneficial for everyone.

Excel is a component of MS Office, It is the product of Microsoft. There are many MS Office version available on the web. You can use any one as you like. Basically, MS providing many official components which are used different official work.

Excel Formulas and Functions

But, Microsoft Excel one the most useable and helpful component, you can use it for the mathematical purpose. MS Excel providing very helpful futures namely Formulas, you can use them to save a couple of time. If you make a Salary Sheet, Balance Sheet or any other accountability sheet, you need to use Formulas for display perfect result.

Here, I have shared a couple of Excel formulas and functions, you can learn one by one. First of All, you have good knowledge of MS Excel then you will understand as well. In this post, I will tell you How You can use Excel Formulas and Functions.

Excel Formulas and Functions with Examples

First of All, we will learn Normal Function such as SUM, MAX, MIN, COUNT, COUNT A, COUNT BLANK. These are common formulas, below I have shared usage of above formula. Then you will understand as well.

BCDEFORMULA
SUM1305030210 =SUM(B3:D3)
MAX5005030500 =MAX(B4:D4)
MIN250502 =MIN(B5:D5)
COUNT3010103 =COUNT(B6:D6)
COUNT ACD2 =COUNTA(B7:D7)
COUNT BLANK305050 =COUNTBLANK(B8:D8)

These are Some Normal Formulas. After that I have shared below Alphabetically Formulas. Which are help to setup a Words as you want.

Here I have Shared Upper, Lower, Proper and Concatenate Formulas. You can see Formula and Results.

BCDFormula
UPPERasifASIF =UPPER(B4)
LOWERASIFasif =LOWER(B5)
PROPERasifAsif =PROPER(B6)
CONCATENATEonlineittutsonlineit tuts =CONCATENATE(B7,” “,C7)

Above You can learned characterized Formulas. Below I have shared Small, Large and Count if Formula with result. You can learn more than from it.

BCDEFormula
SMALL200400500200 =SMALL(B5:D5,1)
LARGE200400500500 =LARGE(B6:D6,1)
COUNTIF2002002003 =COUNTIF(B7:D7,”200″)

After learned Small, Large and Counter Formula. You can learn another helpful and beneficial for formulas such as SumIF, Round, Product, Average. Below I have shared Result Formula, you can get ideas about it.

BCDEFormula
SUMIF900090001500018000 =SUMIF(B3:D3,”=9000″)
ROUND1.56971.57 =ROUND(B4,2)
PRODUCT555125 =PRODUCT(B5:D5)
AVERAGE10121010.67 =AVERAGE(B6:D6)

MS Excel Network Days Formula

After learned above formulas, you can learn another formul Network Days.

Start DateEnd DateResultFormula
Tuesday, April 24, 2007Sunday, April 19, 20152084 =NETWORKDAYS(M4,N4)
Friday, October 2, 2009Sunday, May 29, 2011431 =NETWORKDAYS(M5,N5)
Saturday, July 10, 2010, Friday, April 19, 2013725 =NETWORKDAYS(M6,N6)
Thursday, April 25, 2013Sunday, April 19, 2015517 =NETWORKDAYS(M7,N7)

 MS Excel DatedIF Formula

You can use DatedIF Formula in Excel to get Separate Day, Month and Years. It is very helpful and beneficial formula in Excel. Here I have shared an example.

 

Date of Birth09/06/1995
21 Days=DATEDIF(M15, TODAY(), “Y”)
05 Months=DATEDIF(M15, TODAY(), “YM”)
21 Years=DATEDIF(M15, TODAY(), “MD”)

Above, I have used DatedIF Formula to get Days, Months and Years. You can use DatedIF Formula to get Days, Month and Years As Well.

However, Below I have shared one of the most useable formula namely IF in Excel. IF formula is very helpful and beneficial for you. Below I have shared If Formula different usage.

NameTimePositionMedalFormula
Hakeem1.151 GOLD  =IF(D8<=3, CHOOSE(D8, “GOLD”, “SILVER”, “BROWN”), “UNPLACED”)
Hafeez1.253 BROWN  =IF(D9<=3, CHOOSE(D9, “GOLD”, “SILVER”, “BROWN”), “UNPLACED”)
Majeed1.324 UNPLACED  =IF(D10<=3, CHOOSE(D10, “GOLD”, “SILVER”, “BROWN”), “UNPLACED”)
Razak1.695 UNPLACED  =IF(D11<=3, CHOOSE(D11, “GOLD”, “SILVER”, “BROWN”), “UNPLACED”)
Shareef1.756 UNPLACED  =IF(D12<=3, CHOOSE(D12, “GOLD”, “SILVER”, “BROWN”), “UNPLACED”)
Hameed1.222 SILVER  =IF(D13<=3, CHOOSE(D13, “GOLD”, “SILVER”, “BROWN”), “UNPLACED”)

So, I hope you are learned a lot of Excel Formulas and Functions. I have shared another Formulas such as AND and OR. After learned two formulas, you can learned another Formulas such as Lookup & Vlookup Formulas.

ANDOR
10001000
TRUETRUE
=AND(G3=1000,H3=1000)=OR(G3=1000, H3=1000)

Vlookup Formula Example in Excel

Basically, Vlookup Formula is very important for search the product. After search the product, you can see automatically displayed price. Below I have shared a simple table, you can see different products and price. Then I have entered Product Namely Dove, after that Last Column Showed Price automatically.

Product NamePriceEnter ProductAutomatically Displayed Priced
Pantene190Dove220
Dove220
Colgate100
Fogg399
Ponds70

 

=VLOOKUP(K23,I23:J27,2,0)

Lookup Formula Example in Excel

After learned VLook Formula, You will learn another Formula namely Lookup. Below I have shared example and Formula, you can get ideas about it.

IDNameDesignationSalary
7886HakeemAdministrator PKR      90,000.00
7887HafeezP.Manager PKR      70,000.00
7888MajeedDate Entry Opt PKR      30,000.00
7889RazakPeon PKR      15,000.00
7890ShareefChowkidar PKR      10,000.00

I have made a simple table, in this table you can see Four Columns.  Basically, It is a normal table, I don’t use Formula. But, I will make another table to Search Above Table Record and Display Automatically Result as Well.

IDNameDesignationSalary
7886HakeemAdministrator900000

Basically, I have used Lookup Formula in above tables. After that I simply change ID, then Name, Designation and Salary Automatically Displayed. Below I have shared Formula, you can see with getting ideas about it.

Name Column Used Formula:

=LOOKUP($A$16,$D$7:$G$11,E7:E11)

 

Designation Column Used Formula:

=LOOKUP($A$16,$D$7:$G$11,F7:F11)

 

Salary Column used Formula:

=LOOKUP($A$16,$D$7:$G$11,G7:G11)

However, I hope you have learned a lot of Excel Formulas and Functions in Excel. Thanks for visit and read my Article, If you have any question/suggestion on this Topic. Kindly Comment me or Email me After Received your Email. I will reply with you maximum 24 hours.