Excel Formulas And Functions

     1.    Calculate a Price Reduction.

	A	B	C
1	Pricelist	Price Reduction	-15%
2
3	Product Name	Old Price	New Price
4	M11	11.45	9.73
5	M12	14.00	11.90
6	M13	18.90	16.07
7	M14	34.67	29.47
8	M15	131.99	121.19

Apply the following formula in  Cell C4 and then drag down C4 up to C8

=B4+(B4*$C$1)

     2.    Convert From Hours to Minute

	A	B
1	Calculation with time
2
3	Time	In Minute
4	12:45	765
5	15:57	957
6	5:13	313
7	8:40	520
8	2:44	164

Apply the following formula in  Cell B4 and then drag down C4 up to B8

=A4*24*60

     3.    Convert Currency

	A	B	C
1	Calculation with Currency		0.747
2
3	Product Name	Price $	Price €
4	M11	30	22.41
5	M12	40	29.88
6	M13	50	37.35
7	M14	90	67.23
8	M15	100	74.70

Apply the following formula in  Cell B4 and then drag down C4 up to B8

=B4*$C$1

     4.    Calculate Sales Taxes

	A	B	C
1	Tax	Net Amount	Gross Amount
2	8%	120	129.60

Apply the following formula in  Cell C2

=B2+(B2*A2)                                                           

     5.    Determine Average output

In a worksheet, the start and end production dates of a machine’s operation are given, as well as its output during this period. How do you calculate the average daily production?

	A	B
1	End Date	10/08/2004
2	Start Date	11/13/2002
3	Output	55900
4
5	Average output per day	80.43

Apply the following formula in  Cell B5

=B3/(B1-B2)

     6.    Calculate Total with Percentage

	A	B
1	List Prices	25,500
2	Tax	8%
3	Discount	10%
4
5	Total Price	24,786

Apply the following formula in  Cell B5

=B1*(1+B2)*(1-B3)

     7.    Calculate Net Income

To calculate Net Income, it is necessary to consider the tax percentage, using the following calculation.

	A	B
1	Tax	33%
2	Income	3,500
3	Tax Amount	1,155
4	Net Income	2,345

Apply the following formula in Cell B3

=B2*B1

And Apply the following formula in cell B4

=B2-B3

     8.    Calculate the percentage of price Reduction

A digital camera is on sale. The camera’s original price is 250, but it is now available for 131. What is the percentage of the reduction?

	A	B
1	Old Price	250
2	New Price	131
3	Diff.	119
4	Diff.%	47.60%

Apply the following formula in Cell B3

=B2-B3

And Apply the following formula in cell B4

=B4/B2

Date and Time Function

      9.    Today

	A
1	Current Date

Formula =Today()

  

     10.    Datevalue Function to Recalculate Dates Formatted as Text

	A	B	C
1	Start	End	Days
	11/3/2009	12/4/2009	31
3	1/21/2008	3/13/2008	52
4	2/12/2018	3/12/2018	28
5	4/18/2017	8/2/2017	106

Apply the following formula in  Cell C2 and then drag down C2 up to C5

=DATEDIF(A2,B2,”D”)

     11.    Use the Networkdays Function to Determine the number of workdays 

In this example, a project has to be scheduled. Each of the five steps has fixed start and end dates. To determine the number of complete workdays between the start and end dates, the NETWORKDAYS function from the analysis Tool Pak add-in can be used. This function excludes weekend’s ad any dates identified as non-workdays and holidays.

	A	B	C	D	E
1		Start	End	Holidays	Workdays
2	Step 1	11/08/10	11/22/10	11/19/05	11
3	Step 2	11/23/10	12/03/10	12/01/05	9
4	Step 3	12/04/10	12/13/10		6
5	Step 4	12/14/10	12/23/10		8
6	Step 5	12/24/10	01/03/11		7

Apply the following formula in  Cell E2 and then drag down E2 up to E6

=NETWORKDAYS(B2,C2,D2:D3)

FINANCIAL FUNCTIONS

     12.        The PV function to decide the amount to Invest

The present value of an investment.

	A	B
1	Return Per Year	55,000
2	Number of periods	5
3	Invest rate	500%
4	Invest (Max)	108,866.81

Apply the following formula in cell B4

=PV(B3/12,B2,B1)

     13.        Calculate Periods for Annuity

	A	B
1	Present Value	0
2	Future value	1,00,000
3	Interest Rate	5%
4	Annual payment	5,000
5
6	Years	14.206699

Apply the following formula in cell B6

=NPER(B3,-B4,-B1,B2,0)

     14.  PMT Function

	A	B
1	Loan Amount	5,000
2	Interest rate	4.50%
3	Periods (term in months)	60
4	Compounding periods per year	12
5
6	Monthly payment	93.22

Apply the following formula in cell B6

=PMT(B2/12,B3,-B1)

   15.  RATE Function

	A	B
1	Present Value	25000
2	Time duration (year)	6
3	Monthly payment	500
4
5	Interest Rate	1%

Apply the following formula in cell B5

=RATE(B2*12,B3*-1,B1)

   16.  FV Function

The future value formula is used in essentially all areas of finance. In many circumstances, the future value formula is incorporated into other formulas. As one example, an annuity in the form of regular deposits in an interest account would be the sum of the future value of each deposit.

	A	B
1	Interest Rate	3.50%
2	Number of Periods	5
3	Monthly amount	500
4
5	Future Value	32,733.06

Apply the following formula in cell B5

=FV(B1/12,B2*12,B3)

     17.  NPV Function

The NPV (Net Present Value) function on excel calculates the Net Present Value for periodic cash flows, based on a supplied discount rate, and a series of payments. The NPV in Excel is generally leveraged under Financial calculation.

	A	B
1	Interest Rate	3.50%
2	Number of Periods	5
3	Monthly amount	500
4
5	Net Present Value	498.55

Apply the following formula in cell B5

=NPV(B1/12,B3)

IF FUNCTION

     18.   

	A	B	C
1	Name	Score	Result
2	Rajeev	92	Pass
3	Komal	85	Pass
4	kriti	65	Fail

Apply the following formula in cell C2 and drag upto C4

=IF(B2>=70,”Pass”,”Fail”)

19.       IF STATEMENT FOR TEXT WITH PARTIAL MATCH

If you want to base your condition on a partial match rather than exact match, an immediate solution that comes o mind is using wildcard characters (* or ?) in the logical test argument. However, this simple and obvious approach won't work. Many Excel functions accept wildcards, but regrettably IF is not one of them.

A solution is to use IF in combination with ISNUMBER and SEARCH (case-insensitive) or FIND (case-sensitive) functions.

	A	B	C
1	Product	Delivery Status	Action required
2	Cherries	Delivered	No
3	Bananas	In transit	Yes
4	Apples	Delivered	No

Apply the following formula in cell C2 and drag upto C4

=IF(ISNUMBER(SEARCH(“delivered”,B1)),”No”,”Yes”)

SUMIF Function

    20.       Numbers

The SUMIF function below (two arguments) sums values in the range A1:A5 that are less than or equal to 10.

	A
1	10
2	1
3	7
4	20
5	3
6	21

 Apply the following formula in cell A6

=SUMIF(A1:A5,”<=10”)

    21.       Text

The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 contain exactly circle.

	A	B
1	Circle2	10
2	Triangle	1
3	Circle	7
4	Circle8	20
5	Triangle5	3
6		7

Apply the following formula in cell B7

=SUMIF(A1:A5,”circle”,B1:B5)

     22.       Dates

The SUMIF function below sums the sales after January 20th, 2018.

	A	B
1	1/21/2018	10
2	9/25/2017	1
3	8/3/2018	7
4	8/30/2017	20
5	4/6/2017	3
6		17

Apply the following formula in cell B6

=SUMIF(A1:A5,”>”&DATE(2018,1,20),B1:B5)

     23.       SUMIF Function to determine Sales of a Team

	A	B	C	D	E	F
1	Team	Employee	Sales Today		Team	Sales
2	2	Dilshad	1,955		1	18363
3	1	Saniya	7769		2	17203
4	2	Mumtaj	6514		3	6448
5	3	Prakriti	1698
6	3	Shristi	4750
7	1	Samredhi	2890
8	2	Sadhasiv	8405
9	1	Dershan	7704
10	2	Darpan	329

Apply the following formula in cell F1 and Drag the Cell F1 to F3

=SUMIF(A2:A10,E2,C2:C10)

     24.       SUMIF Function to sum costs Higher than $1,000

	A	B	C	D
1	Phases	Costs	Criteria	1,000
2	Phase 1	750	SUMIF Result	4521
3	Phase 2	1020
4	Phase 3	999
5	Phase 4	1001
6	Phase 5	2500

Apply the following formula in cell D2

=SUMIF(B2:B11,”>”&D1)

     25.       SUMIF Function to SUM Costs up to a Certain Date

	A	B	C	D
1	Dates	Costs	Until	11/16/10
2	11/09/10	583	SUMIF Result	3395
3	11/10/10	174
4	11/11/10	881
5	11/12/10	823
6	11/13/10	93
7	11/14/10	417
8	11/15/10	258
9	11/16/10	166
10	11/17/10	940
11	11/18/10	280

Apply the following formula in cell D2

=SUMIF(A2:A11,”<=”&D1,B2:B11)

    26.       COUNTIF Function to calculate an Attendance List

	A	B	C	D	E	F	G	H
1	Date	W/D	Seema	Prerna	Karan	Pranay	Sheetal	Presence
2	10/01/2019	Mon	P	P	P	P	P	5
3	10/02/2019	Tue		p		p	p	3
4	10/03/2019	Wed	p	p	p	p		4
5	10/04/2019	Thu	p	p		p	p	4
6	10/05/2019	Fri		p	p	p	p	4
7	10/06/2019	Sat	p	p	p	p	p	5
8
9			4	6	4	6	5

Apply the following formula in cell C9 and then drag C9 to G9

 =COUNTIF(C2:C7,”P”)

                                                                                                                  

    27.       Calculate Net and Corresponding Gross Prices

	A	B	C
1	Calculate Tax
2	Tax	Gross Price	Net Price
3	7%	100	107
4	7%	108.411	116

Apply the following formula in cell C3

=B3+(B3*A3)

Apply the following formula in cell B4

=C4/(1+A4)

    28.       Determine the Economic Value of a Product

	A	B	C	D
1	Product	Cost	Price	Margin
2	Product 1	15,911	17,249	1.084
3	Product 2	19,320	16,982	0.878
4	Product 3	18,078	19,297	1.067
5	Product 4	17,967	19,137	1.065

Apply the following formula in cell D2 and then drag D2 to D5

=C2/B2

    29.       Determine the weekly Sales for each Department

	A	B	C	D	E
1	Department	Name	Weekly Sales	Department	Sales
2	Food		1675	Food	5353
3	TV		1691	TV	3414
4	Perfume		1522	Pc	3547
5	Pc		1960	Perfume	3162
6	Food		1730
7	Pc		1587
8	TV		1723
9	Food		1948
10	Perfume		1640

Apply the following formula in cell E2 and then drag E2 to E5

=SUMIF(A2:A10,D2,C2:C10)

    30.       Determining Inventory Value

	A	B	C
1	Items	Quantity	Price
2	Printer	5	99.99
3	Pen	10	5.99
4	Mouse pad	29	3.45
5	Joystick	15	19.99
6	Flat Screen	11	159.99
7	Pc	9	999
8	Total	11710.64

Apply the following formula in cell B2

{=SUMPRODUCT(C2:C7*B2:B7)}

    31.       Determine Amount of Invest

	A	B
1	Yearly Return	1,50,000
2	Length of investment	5
3	Interest Rate	4.50%
4	Cost if Investment	658,496.51

Apply the following formula in cell B4

=-PV(B3,B2,B1)

    32.        XNPV and XIRR

Formula: =XNPV(discount rate, cash flows, dates)

If you’re an analyst working in investment banking, equity research, or financial planning & analysis (FP&A), or any other area of corporate finance that requires discounting cash flows, then these formulas are a lifesaver!

	A	B	C	D	E	F	G
1	Dates	5/18/18	12/31/18	9/12/19	12/25/19	5/8/20	12/31/20
2	Cash Flow	1000	1000	1000	1000	1000	1000
3	Discount	10%
4	XNPV	5289.36

Apply the following formula in cell B4

=XNPV(B3,B2:G2,B1:G1)

    33.       Use the Sumproduct Function to Multiply and Sum at the Same Time

	A	B	C	D	E
1	Date	Days	Team	Hours	Payment
2	11/15/18	Monday	2	6	298
3	11/16/18	Tuesday	1	7	343
4	11/17/18	Wednesday	3	9	441
5	11/18/18	Thursday	1	3	147
6.	Team	1	490

Apply the following formula in cell B4

=SUMPRODUCT((C2:C5=B6)*(E2:E5))

Depreciation

Excel offers five different depreciation functions. We consider an asset with an initial cost of $10,000, a salvage value (residual value) of $1000 and a useful life of 10 periods (years).

SLN

The Microsoft Excel SLN function returns the depreciation of an asset for a period based on the straight-line depreciation method. The SLN function is a built-infunction in Excel that is categorized as a Financial Function. It can be used as a worksheet function (WS) and a VBA function (VBA) in Excel

SYD

The Excel SYD function returns the "sum-of-years" depreciation for an asset in a given period. The calculated depreciation is based on initial asset cost, salvage value, and the number of periods over which the asset is depreciated.

DB

The Microsoft Excel DB function returns the depreciation of an asset for a given time period based on the fixed-declining balance method. The DB function is a built-in function in Excel that is categorized as a Financial Function. It can be used as a worksheet function (WS) in Excel.

DDB

The Microsoft Excel DDB function returns the depreciation of an asset for a given time period based on the double-declining balance method. The DDB function is a built-in function in Excel that is categorized as a Financial Function. It can be used as a worksheet function (WS) and a VBA function (VBA) in Excel.

VDB

The Microsoft Excel VDB function returns the depreciation of an asset for a given time period based on a variable declining balance depreciation method. The VDB function is a built-in function in Excel that is categorized as a Financial Function. It can be used as a worksheet function (WS) in Excel.

     34.

	A	B
1	Cost	10000
2	Salvage	1000
3	Life	10
4
5	Period	SLN	SYD	DB	DDB	VDB
6	1	900	1636.36	2060	2000	2000

Apply the following formulas in Cell

B6

=SLN(B1,B2,B3)

C6

=SYD(B1,B2,B3,A6)

D6

=DB(B1,B2,B3,A6)

E6

=DDB(B1,B2,B3,A6)

F6

=VDB(B1,B2,B3,A6--1,6)

35. Use the HYPERLINK function to link to the internet.

	A	B	C
1	google.com	Search	Click tosearch
2	Microsoft.com	Learn about Microsoft	Click tolearn about microsoft
3	Softlineinstitute.com	Visit website	Click tovisit website
4	Amazon.com	Look up books	Click tolook up book

Apply the following formula in cell C1 to C4

=HYPERLINK(“http://”&A1,”click to” &B1)

      36.         Calculate Sales Tax

	A	B
1	Price	320
2	Tax Rate	7.50%
3	Sales Tax	24
4	Total Paid	344

Apply the following formula in cell B3

=B1*B2

Apply the following formula in cell B4

=B1*B2