learn exxel formulae

7/28/2019 Learn ExXel Formulae

1/76

Learn MS-Excel With Easy Steps

This Assignment is made for Professional Students of ICMAP.

This is a comprehensive and easy to learn assignment to learning the MS-ExThis assignment provides you essential know-how for developing businessSpreadsheets, Charts, Pivot Tables, Logical Calculations and many other thingMS-Excel.

In this assignment I am introducing you to the wide variety of topics, I structureassignment so that you learn MS-Excel in a carefully designed and step-by-stepictorial way. You understand at least the fundamental concepts of howMS-Excel works and Advance features ofMS-Excel with easy help.

Finally, I would like to say that I made this assignment easy for every person, aenabled the user to improve the style of Spreadsheets.

S. Nadeem ShahIT Division.

Institute of Cost and Management Accountants of Pakistan (ICMAP)E-mail: [email protected]

[email protected]

[email protected]

Phone: 9243900. Ext. 237 (Computer Lab)

Mobile: 0320-5085330


2/76

el.

s in

d thep

nd


3/76

S# Name Test1 Test2 Test3 Total AVG REMARKS

1 Zahid 35 24 24 83 41.52 Akber 56 78 45 179 89.53 Nida 12 13 23 48 244 Moon 56 76 34 166 835 Noor 26 75 87 188 946 Faisal 53 56 78 187 93.57 Benson 65 67 89 221 110.5

65 13 89

23

1 Calculate Total

2

3 Calculate Maximum Value of Test1 and Minimum Value of Test2

4 Calculate Remarks using IF Condition

Type in Cell C10

Type in Cell D10

Type in Cell G3 and Copy the formula =AVERAGE(C3:E3)

Type in Cell H3 and Copy the formula

Parts of If Condition

=IF(F3>100,"GOOD","POOR")

=MAX(C4:C9)

=MIN(D3:D9)

Total 100 , " Good"

Tips

=MIN(FIRSTRANGE:LASTRANGE)

HELP WITH INSTRUCTIONS

=SUM(FIRST RANGE:LAST RANGE)

IF(Logic,"True Value","False Value")

Calculate AVERAGE

Created by Nadeem ShahComputer Division ICMAP

SUM, AVERAGE, MAX, MIN, AND SIMPLE "IF" CONDITION.

Calculate Total, Average, Maximum, Minimum and Remarks byusing Excel Functions. If you feel problem to solve see the help that

is given below.


4/76

Student Name Roll# Test1 Test2 Test3 Test4 Test5 TOTAL Marks Obtained Per% Remarks

Sana Khan 101 45 67 87 86 35 500 320 64 good

M. Ali 102 23 76 68 54 76 500 297 59.4 Bad

Kamran Saeed 103 34 67 78 75 82 500 336 67.2 good

Nadia Barlas 104 94 89 79 90 88 500 440 88 exlt

Nadeem Syed 105 23 56 74 33 67 500 253 50.6 Bad

Arif Mustafa 106 36 78 83 93 77 500 367 73.4 V.goodDavid 107 38 47 46 59 34 500 224 44.8 Bad

Furqan Haider 108 56 67 78 87 45 500 333 66.6 good

M. Ali 109 88 89 99 98 89 500 463 92.6 exlt

Majid Bilal 110 12 14 34 14 24 500 98 19.6 Bad

1 Calculate Marks Obtain,2 Calculate Per%3 Calculate Remarks Using Nested IF Condition

This time you are watching a Nested IF Condition in which you can add more than one IF Simultaneously.

IF(Per%>=80,"EXLT",IF(Per%>=70,"V.GOOD",IF(Per%>=60,"GOOD","BAD")))4 Calculate Grade Using Nested IF Condition

IF(Per%>=80,"A+",IF(Per%>=70,"A",IF(Per%>=60,"B","FAIL")))

Type in Cell I3 =SUM(C3:G3)

Type in Cell J3 =I3/H3*100

Type in Cell K3 =IF(J3>=80,"EXLT",IF(J3>=70,"V.GOOD",IF(J3>=60,"GOOD","BAD")))

GRADE SHEET

HELP WITH INSTRUCTIONS

MORE HELP

Nested "IF" Conditions.


5/76

Type in Cell L3 =IF(J3>=80,"A1",IF(J3>=70,"A",IF(J3>=60,"B","FAIL")))



6/76

Grade

B

fail

B

A1

fail

Afail

B

A1

fail

WHAT IS THIS?

Nested IF Condition:Up to seven IF functions can be nested as value_if_true and value_if_false arguments toconstruct more elaborate tests.

You can use the following nested IF function:

=IF(PER>=80,"A+",IF(PER>=70,"A",IF(PER>=60,"B","FAIL")))


7/76


8/76

S# NAME BASIC SALE1 SALE2 SALE31 NOMAN 2500 900 800 2502 QADIR 2000 150 250 900

3 ARIF 2200 100 300 5004 ZAHID 2500 450 250 4005 BABAR 2400 150 100 400

1 Calculate Tot.Sale

2 Calculate All.1 Using Nested IF ConditionIF(Tot.Sale>=1000,Basic*35%,IF(Tot.Sale>=8

3 Calculate All.2 Using IF(OR) ConditionIF(OR(Tot.Sale>800,Basic=2500),1000,500)

4 Calculate All.3 Using IF(AND) ConditionIF(AND(Tot.Sale>800,Basic=2500),1000,500)

Type in Cell G3 =SUM(D3:F3)

Type in Cell H3 =IF(G3>=1000,C3*35%,IF(G3>=800,C3*25,"TRY AGAIN"))

Type in Cell I3 =IF(OR(G3>800,C3=2500),1000,500)

Type in Cell J3 =IF(AND(G3>800,C3=2500)1000,500)

Beverage Compan

HELP WITH INSTRUCTI

MORE HELP

IF(AND), IF(OR)

Created bComputer


9/76

TOT.SALE ALL.1 ALL.2 ALL.31950 625 1000 10001300 500 1000 500

900 550 1000 5001100 625 1000 1000650 Try Again 500 500

00,Basic*25%,"Try Again"))

NS WHAT IS THIS?

IF(OR(Returns TRUE if any argument is TRUE;returns FALSE if all arguments are FALSE.SyntaxIF(OR(logical1,logical2,...),"True","False")

IF(AND(Returns TRUE if all arguments are TRUE;returns FALSE if any argument is FALSE.SyntaxIF(AND(logical1,logical2,...),"True",'False")

y Nadeem ShahDivision ICMAP


10/76

Student Name ID# Test1 Test2 Test3 Test4 Test5 Total

Haider 6 67 56 89 68 80 500

Ali 3 56 57 78 46 47 500

Babar 4 78 89 88 90 87 500

Zahid 1 98 99 91 95 90 500

Faisal 7 34 23 22 12 67 500

Ameen 2 67 78 89 67 97 500

Rasool 5 24 34 34 23 34 500

Qadir 8 67 84 66 77 86 500

Yahya 9 23 46 57 87 90 500

Alia 10 45 67 45 78 88 500

0 FAIL40 D50 C60 B70 A80 A+

STEP# 1

First you have to Calculate Per% by using this formula in Cell# J4Marks Obtain/Total Marks*100 =I4/H4*100STEP# 2After Calculating Per% we can Find out the Grades by using VLOOKUP Workshee

Type this formula in Cell# K4 =VLOOKUP(J4,$E$16:$F$21,2)

J4$E$16:$F$212 is Col_Index# of Table_Array

Grading Criteria

Grade Book

MORE HELP

is lookup valueRange of table_array

Table_Array is thecomplete table of

informationCol_In

ColuTabl

VLOOKUP FUNCTION



11/76

M. Obtain Per% Grade

360 72 A

284 56.8 C

432 86.4 A+

473 94.6 A+

158 31.6 FAIL

398 79.6 A

149 29.8 FAIL

380 76 A

303 60.6 B

323 64.6 B

Function

WHAT IS THIS

VlookupSearches for a value in the leftmost column of a table, and then returns a value icolumn you specify in the table. Use VLOOKUP instead of HLOOKUP when youlocated in a column to the left of the data you want to find.

SyntaxVLOOKUP(lookup_value,table_array,col_index_num )

Lookup_value is the value to be found in the first column of the array. Lookupreference, or a text string.

Table_array is the table of information in which data is looked up. Use a referename, such as Database or List.

Col_index_num is the column number in table_array from which the matching

ex is themn# of_Array

Lookup_Value


12/76

n the same row from ar comparison values are

value can be a value, a

nce to a range or a range

value must be returned.


13/76

NAME BASIC SALE1 SALE2 SALE3 TOT.SALEZ 2500 450 250 400 1100

Y 4000 240 200 124 564

Q 2000 150 250 400 800N 2500 200 300 250 750F 3400 56 67 89 212D 3500 365 456 345 1166

C 5000 45 67 678 790C 3000 56 78 89 223B 7000 346 890 456 1692

B 6000 245 666 78 989B 5600 456 345 67 868B 2400 150 100 400 650B 2200 145 234 50 429

A 5000 367 78 98 543A 4000 356 67 89 512A 3400 340 56 360 756A 3000 123 45 67 235

A 2300 230 56 345 631A 2200 100 300 500 900

Q1 Sort Data in Ascending Order (Sort by Name in Ascending Order and t

Q2 Filter The Records (Basic >3000 and Total Sale Auto Filter Command

You will see the drop down buttons with every fieldYou can use these buttons to Filter your data

SORTING AND FILTER

MORE HELP

Created by Nadee


14/76

Computer Division


15/76

en by Basic Descending Order)

he

WHAT IS THIS?

SORTINGSort Command is used to arrange tha Information in selectedrows or lists Alphabetically, Numerically or by date in Ascending

order or Descending order. A TO Z or Z TO A, 1 TO 100or100 TO 1, 1-1-1998..to.30-12-2002DATA>SORT

FILTERThe quickest way to select only those items you want to displayin a list.DATA>FILTER>AUTO FILTER

Shah


16/76

ICMAP


17/76

1st Match 2nd Match 3rd Match 4th Match

Pakistan 234 301 298 156Australia 235 256 289 158

Method 1Create a Column Chart of SHEET1Select Sheet # 1 from A4 TO F6, Press F11 Function Key the Quick Chart Will be created on a new s

NAME TOTAL SALESHAH 10000ALI 8900KAMAL 3450SONIA 6789SANA 12345ZIA 13456

YASEEN 10988QAMAR 6789ALAM 45677Method 2Create a Pie Chart of SHEET 2Select Sheet # 2 from A14 TO B23, Click on Chart Wizard Icon and follow all instructions.

Pakistan VS Australia

CHARTS

SHEET2 FOR CHART

SHEET 1 FOR CHART


18/76

5th Match

213214

eet.

1

10

100

1000

10000

100000

1

0

0.5

1

1.5

2

2.5

3

3.5

4

4.5

Fo

WHAT IS THIS?

About ChartsCharts are visually appealing and make it easy for users to seecomparisons, patterns, and trends in data. For instance, rather thanhaving to analyze several columns of worksheet numbers, you cansee at a glance whether scores are falling or rising , or how thePakistan's score compare to the Australian score.

You can create a chart on its own sheet or as an embedded objecton a worksheet. You can also publish a chart on a Web page. Tocreate a chart, you must first enter the data for the chart on the

worksheet. Then select that data and use the Chart Wizard to stepthrough the process of choosing the chart type and the variouschart options.

A chart is linked to the worksheet data it's created from and isupdated automatically when you change the worksheet data.

Create a ChartSelect the cells that contain the data that you want to appear in thechart.

Click Chart Wizard Button or go into the Insert menu and selectChart Command than follow the all instructions carefully.

IMPORTANT

You can make a quick chart by pressingF11 key



19/76

1000

246.2352531

260.3854253

971.648

573.6544681

600

428

1991

o Bar

North

South

SOME EXAMPLES OF CHA


20/76

Name Test1 Test2 Test3Zahid Nill Nill 89 =COUNT(B3:B9)

Akber 56 78 45 =COUNT(C3:C9)Nida 99 13 23 =COUNT(D3:D9)

Moon 56 99 99Noor 26 99 87Faisal 89 99 78

Benson Nill 67 89

TOTAL 5 6 7

COUNT FUNCTION

MORE HEType in Cell B10

Type in Cell C10

Type in Cell D10

Count the Cellsthat contains Numbers



21/76


22/76

= 5= 6= 7

P WHAT IS THIS?

Counts the number of cells thatcontain numbers and numberswithin the list of arguments. Use

COUNT to get the number ofentries in a number field in a rangeor array of numbers.

SyntaxCOUNT(value1,value2, ...)


23/76


24/76

WHAT IS THISEmp. Name Basic Bonus SumIf Adds the cells specified by a given

Kashif 4000 2000 CountIf Counts the Cells specified by a giAli 3000 1500

Noman 2500 1250 SyntaxSidra 6000 1200 =SUMIF(range,"criteria")Romana 4000 2000 =COUNTIF(range,"criteria")Nasir 7000 700Faisal 5000 2500Nadeem 4000 2000Jalal 2500 1250

Rufi 5000 2500Ayjaz 3000 1500Lilly 6000 1200

52000

12 5

Type in Cell C17

Type in Cell C18

SumIf & CountIf Functions

=SUMIF(C5:C16,

MORE H

=COUNTIF(C5:C1

11000

Sum the Bonus that have value>2000 using Sumif Function.

Count the Bonus that have value

>2000 using Sumif Function.



25/76

criteria.

en criteria.

">2000")

LP

,">2000")


26/76

1

2

3

4

56

7

8

9

101112131415

16

1718192021

22

23

24

25

26272829303132

A B C D

Cost of Computer 30000Salvage Value 5000

Life 10

Period in Years Double Declining Straight Line Sum of Years Digit1 $6,000.00 Rs.2,500.00 Rs.4,545.452 $4,800.00 Rs.2,500.00 Rs.4,090.913 $3,840.00 Rs.2,500.00 Rs.3,636.364 $3,072.00 Rs.2,500.00 Rs.3,181.825 $2,457.60 Rs.2,500.00 Rs.2,727.27

6 $1,966.08 Rs.2,500.00 Rs.2,272.73

7 $1,572.86 Rs.2,500.00 Rs.1,818.188 $1,258.29 Rs.2,500.00 Rs.1,363.649 $33.16 Rs.2,500.00 Rs.909.09

10 $0.00 Rs.2,500.00 Rs.454.55

Double Declining Rs.6,000.00 =DDB($B$3,$B$4,$B$5,A9)Straight Line Rs.2,500.00 =SLN($B$3,$B$4,$B$5)Sum of Years Digit Rs.4,545.45 =SYD($B$3,$B$4,$B$5,A9)

YEARLY DEPRECIATION

DDB, SLN, SYD METHODS

MORE

CreatComp


27/76

1

2

3

4

56

7

8

9

101112131415

16

1718192021

22

23

24

25

26272829303132

E F G H I J K L M

WHAT IS THIS?

DDBReturns the depreciation of an asset for a specified period using the

double-declining balance method .Syntax

DDB(cost,salvage,life,period)

SLNReturns the straight-line depreciation of an asset for one period.SyntaxSLN(cost,salvage,life)

SYDReturns the sum-of-years' digits depreciation of an asset for a specified period.SyntaxSYD(cost,salvage,life,period)

Type this Formula in Cell B9 and copy the formula to the B18Type this Formula in Cell C9 and copy the formula to the C18Type this Formula in Cell D9 and copy the formula to the D18

ELP

d by Nadeem Shahter Division ICMAP


28/76

NAME BASIC REGION SALE1 SALE2 SALE3ALI 5000 EAST 367 78 98

BABAR 4000 WEST 356 67 89

SAFDAR 3400 EAST 340 56 360ZIA 3000 NORTH 135 45 67BILL 2300 SOUTH 230 56 345ALI 2200 EAST 100 300 500

SILVESTER 7000 WEST 346 890 456NOMAN 6000 SOUTH 245 666 78BABAR 5600 NORTH 456 345 67

YOUSUF 2400 EAST 150 100 400AZIZ 2200 WEST 145 234 50

AKRAM 5000 EAST 45 67 678ALI 3000 EAST 56 78 89RAO 3500 SOUTH 365 456 345

MALIK 3400 NORTH 56 67 89AFSAR 2500 WEST 200 300 250RAEES 2000 SOUTH 150 250 400

RASHEED 4000 EAST 240 200 124ZIA 2500 SOUTH 450 250 400

STEP# 1 First select the above sheet from Cell A2 to G21STEP# 2 Go into Data Menu and select the command Pivot Table and Pivot ChSTEP# 3 You will see the Pivot Table Wizard now Press the next button

STEP# 4 Again a small window appears and ask for Data Range that you haveSTEP# 5 Now Wizard will ask you where do you want to put the Pivot Table Rep

On New WorksheetExisting WorksheetYou will see that the New Worksheet Option is already selected you ca

STEP# 6 Now Press the Layout ButtonYou can see the Lay Out dialog box, in this box you can set fields for t

page row data column You have four main areas and group of field buttonsPage Row Data Column

NAME

BASIC

REGION

SALE1

PIVOT TABLE REPORT

MORE HELP WITH INSTRUCTIONS

ColumnPage


29/76

SALE2

SALE3

TOT.SALE

From the group of field button on the right, drag the fields that you wanin the diagram.

To include a data field drag the field onto the DATA area.Note:

STEP#7 When you are satisfied with the layout, press OK, and press Finish.

Data area is a different summary function to calculate tYou should place all Numeric Data Fields in t

You can include more than one fields in thi


30/76

TOT.SALE543512

756247631900

1692989868

650429

790223

1166

212750800564

1100

rt Report

elected before, Press Nextort

n change the option if you need

e Pivot Table

WHAT IS THIS?

Pivot Table?A PivotTable report is an interactive table that you can use to quicklysummarize large amounts of data. You can rotate its rows and columnsto see different summaries of the source data, filter the data bydisplaying different pages, or display the details for areas of interest.

When to use a Pivot Table ReportUse a PivotTable report when you want to compare related totals,especially when you have a long list of figures to summarize and youwant to compare several facts about each figure. Use PivotTablereports when you want Microsoft Excel to do the sorting, subtotaling,and totaling for you.



31/76

t onto the ROW and Column area

he numeric fieldshis area

area


32/76

Person Name Type Unit Sold Unit Price Total PriceAyjaz TV 4 10000 40000

Ayjaz TV 3 10000 30000

Ayjaz Car 1 200000 200000

Babar TV 1 10000 10000Babar Printer 1 6000 6000

Faisal Computer 3 50000 150000

Faisal Ribbon 4 300 1200

Faisal Scanner 2 3000 6000

Kashif Computer 5 50000 250000

Nadeem Computer 7 50000 350000

Nadeem TV 6 10000 60000

Nadeem Car 2 200000 400000

Nadeem Printer 5 6000 30000

Nadeem Dimm 10 500 5000

Rufi VCR 4 12000 48000

Rufi Printer 5 6000 30000Rufi Computer 6 50000 300000

Rufi Ribbon 9 300 2700

Zubair Car 1 200000 200000

Zubair VCR 2 12000 24000

STEP# 1 Sort the list by the column for which you want toCalculate subtotals.

STEP# 2 Select data from A1 to E21

STEP# 3 On the Data menu, click Subtotals.You will see a new window

STEP# 4 In the At each change in box, click the column thatcontains the group for which you want subtotals.

STEP# 5 In the Use function box, click the function you want touse to calculate the subtotals.

STEP# 6 In the Add subtotal to box, select the check boxes forthe columns that contain the values for which youwant to subtotals.

STEP# 7

MORE HELP

Subtotals

WHAT IS THISubtotal?

Microsoft Excel cancalculating subtotalautomatic subtotals,and the list must besubtotals.

Figur



33/76

?

automatically summarize data byand grand total values in a list. To use, your list must contain labeled columnssorted on the columns for which you want

for help


34/76

47

text

1

15

2.54

#DIV/0!5

#DIV/0!

Conditional Formatting Example #2 - Hiding Error Values

This example illustrates how to use Conditional Formatting to hideerror values that are returned by formulas. In this example, cells H21and H23 would normally display the #DIV/0! error code. TheConditional Formatting that has been applied sets the font color of thecells containing an error value to match the background of theworksheet.

To see the conditions applied to the cells, select cell H19, and then

click Conditional Formatting on the Format menu.

Conditional Formatting

Conditional Formatting allows you to change the formatting applied tocell depending on the current value of the cell. This can makeauditing large worksheets much faster by automatically highlighting

exceptions. Conditional Formatting allows you to apply up to threeseparate conditions to a cell.

Conditional Formatting allows you to change the font style, borders,and cell patterns.

Conditional Formatting Example #1

Cells H10:H14 contain three Conditional Formatting rules that willchange the formatting of the cells depending on the values enteredinto the cells.

To see the conditions applied to the cells, select cell H10, and thenclick Conditional Formatting on the Format menu.

Created by Nadeem Shah


35/76

10 420 53040 850


36/76

Data Validation

Data Validation Example #1 - Restricting Entry to Numeric Values

Cells H10:H13 have been formatted with a validation rule thatrestricts cell entries to numeric values. This example utilizes theStop style for the Error alert, which prevents you from making aninvalid entry into the selected cell.

To see the Validation settings for this example, select cell H9, and

then click Validation on the Data menu.

Data Validation Example #4 - Restricting Cell Entry to a List of Values

Cells H41:H45 have been formatted with a validation rule thatrestricts the entries to a list of values. The list of valid entries iscontained in cells M41:M49. When you select a cell within therange H41:H45, a dropdown arrow appears on the cell. When youclick the arrow, the list of valid entries is displayed. You can simply

click the entry that you wish to make. This example utilizes theStop style for the Error alert, which prevents you from making aninvalid entry in the cell.



Data Validation Example #2 - Restricting the Length of a Text Entry

Cells H19:H23 have been formatted with a validation rule thatrestricts the length of text entries to seven characters or less. This

example utilizes the Warning style for the Error alert, which givesyou the option to cancel the current entry, or enter the invalid valueinto the selected cell.



Data Validation Example #3 - Restricting Entry to a Range of Whole Numbers

Cells H30:H34 have been formatted with a validation rule that will

alert you if you do not enter a whole number between 1 and 10.This example utilizes the Information style for the Error alert, whichinforms you of an invalid entry, but allows you to keep the currentvalue.



WH

DATA

Data Vcan becomm

STEP1. Go i

2. Set


37/76



38/76

T IS THIS?

VALIDATION

alidation allows you to set up restrictions for the values thatentered into a cell. The following examples present severaln scenarios for using Data Validation.

:nto Data Menu and secect the command "Validation"

the Validation Criteria by using setting tab.


39/76

Use of Precedents and Dependents

Mouse Keyboard Printer Joystick TOTALUNIT PRICE 100 200 10000 1200 11500QUANTITY 20 20 5 10 55TOTAL PRICE 2000 4000 50000 12000 68000

68000

Step1 Go into Tools Menu and place mouse pointer on Auditing Command and then click on Show

Step2 Now Place the Cell Pointer on Cell# F46, and then click on Trace Precedents ButtonOne blue tracer arrow will shows that cells B46 through E46 are Precedents of Cell# F46

Step3 Place the Cell Pointer on Cell# B48, and click on Trace Dependents ButtonNow two tracer arrows will show that the Cell# B48 is Dependents of Cell# F48 and Cell# G4

Step4 If you want to remove all Tracing Arrows click on Remove All Arrows Button.

2345

ITEM LIST

Instructions

GRAND TOTAL

More About Auditing

Trace Precedents

Remove Precedents Arrows

Trace Dependents

Remove Dependents Arrows

Clear V

Trac

Circle I

New

Remove All Arrows

Auditing Toolbars

Trace Error ButtonIf the active cell contain an error value such as#VALUE, #NAME? or #DIV/0, draws tracer errows to the

Place the Cell PCell# G66 andTrace Error ButtOne Tracer Arroshow that the C

E


40/76

#NAME?

734586

. an rror a ue.

New Comments ButtonInserts a comment at the insertion point.

Place the CellCell# G69 andComment Buttotype your own c

for active cell.

Circle Invalid DataIdentifies all cells that contain values that are outside the limitsyou set by using the Validation command on the Data menu.To see what data restrictions and messages are in effect for acell, set the Validation Rules on the Data and then click on theCircle Invalid Data Button.

Select cells froGo into Data MValidation ComValidation Rule

Allow only Wholpress ok.

Now click on CiButton, you will

on No. 3, 4, 5, bare invalid for th



41/76

uditing Tool Bar

WHAT IS THIS

AUDITINGMicrosoft Excel provides tools that help you

track down problems on your worksheets.

For example, the value you see in a cell may bethe result of a formula, or it may be used by aformula that produces an incorrect result. Theauditing commands graphically display, ortrace, the relationships between cells andformulas with tracer arrows.

When you audit a worksheet, you can trace theprecedents (the cells that provide data to aspecific cell) or you can trace the dependents(the cells that depend on the value in a specificcell.

alidation Circles

Precedents

nvalid Data

Comments

ointer onlick on

on.w willell# G66 is

AMPLES


42/76

ointer onlick Newn. Nowomments

G76 to G81 thennu and selectand and set

:

e Number > 5 and

cle Invalid Datasee three circles

ecause these Nos.is range.


43/76

LOOKUP

S. Nos NAME F. NAME T. PHONE ADDRESS1 SAEED ALI 3444455 KARACHI2 RAHIL SHAH KHAN 8978766 LAHORE3 ZAHID ALI NOOR ZAMAN 6767676 KARACHI4 NOMAN BABAR QADIR BHAI 7878888 PINDI5 ASIF KHAN RAFI ULLAH 6676777 HYDERABAD

Type any S. No 3NAME ZAHID ALI

F. NAME NOOR ZAMAN

T. PHONE 6767676

ADDRESS KARACHI

MARRIED N N

FORMULAS USED

ZAHID ALI

NOOR ZAMAN

6767676

KARACHI


44/76

MARRIEDYYNYN

WHAT IS THIS?

Lookup

Returns a value either from a one-row orone-column range or from an array. TheLOOKUP function has two syntax forms:vector and array. The vector form ofLOOKUP looks in a one-row or one-column range (known as a vector) for avalue and returns a value from the sameposition in a second one-row or one-column range. The array form ofLOOKUP looks in the first row or columnof an array for the specified value andreturns a value from the same position inthe last row or column of the array.


45/76

_ H_

A 25,000.00Rs. DIRECTOR 13,750.00B 25,000.00Rs. DIRECTOR 13,750.00

C 15,000.00Rs. ASSISTANT MANAGER 8,250.00

D 10,000.00Rs. OFFICER 5,500.00

E 10,000.00Rs. OFFICER 5,500.00

F 6,000.00Rs. TYPIST 3,300.00

G 4,000.00Rs. PEON 2,200.00

H 15,000.00Rs. ASSISTANT MANAGER 8,250.00

I 10,000.00Rs. OFFICER 5,500.00

J 10,000.00Rs. OFFICER 5,500.00

K 6,000.00Rs. TYPIST 3,300.00

L 4,000.00Rs. PEON 2,200.00

TOTAL: 140000 77000

D3 =55%*B3

E3 =15%*B3

F3 =IF(C3="DIRECTOR",25%*B3,I

G3 =D3+E3+F3

H3 =IF(G3>5000,20%*B3,0)

I3 =IF(B3>20000,15%*B3,I

J15 =G15+H15

K3 =B3+J3

L3 =IF(C3="DIRECTOR",20

K15 =B15+J15

L3 =IF(C3="DIRECTOR",20

N3 =IF(M3>500000,55%*B3,

O3 =I3+L3+N3

Select All Data with Headings, Go into Data Menu, Click Filter, Select Auto Filter, Click D

Filter Records BASIC > 20000EMP_NAME BASIC DESIGNATION H_RENT

A 25,000.00Rs. DIRECTOR 13750

B 25,000.00Rs. DIRECTOR 13750

Max Basic = 25,000.00Rs =MAX(B3:B14)

Instructions for FILTER

Max, Min, Average


46/76

Min Basic= 4,000.00Rs =MIN(B3:B14) Column # 3

Average G_salary 23666.66667 =AVERAGE(K3:K14) Column # 2

EMP_NAME

=IF(AND(D44="A",E44>20000),10000,5000) 10000 A

D44=EMP-NAME AND E44=BASIC 5000 B

Cell Addresses 5000 C

5000 D

5000 E

5000 F

5000 G

5000 H

5000 I5000 J

5000 K

5000 L

Create a Pie Chart

Range

Emp-nam+Basic

ITEM JANUARY Name

Computer 100 Amir Sohail

Mouse 250 Inzemam

Keyboard 200 Afridi

Fan 150 Moeen Khan

Tv 300 =VAR(E69:E72)

Total 1000 =STDEV(E69:E72)

CountIF= 2

Description

Use IF Condition with AND Chart Range & IF C

Use "Count If" Function Calculate "VAR"

=COUNTIF(B69:B73,">200")

A

C

DEF

G

H

IJ

K L

BASI

If the value of D44 equals to "A"AND E44 is greater then 20000

then assing 10000 in C44otherwise put 5000 in C44


47/76

L LL. _ LL. L LL.

3,750.00 6,250.00 23,750.00 50003,750.00 6,250.00 23,750.00 5000

2,250.00 2,250.00 12,750.00 3000

1,500.00 1,000.00 8,000.00 2000

1,500.00 1,000.00 8,000.00 2000

900.00 300.00 4,500.00 0

600.00 200.00 3,000.00 0

2,250.00 2,250.00 12,750.00 3000

1,500.00 1,000.00 8,000.00 2000

1,500.00 1,000.00 8,000.00 2000

900.00 300.00 4,500.00 0

600.00 200.00 3,000.00 0

21000 22000 120000 28000

F(C3="MANAGER",20%*B3,IF(C3="ASSISTANT MANAGER",15%*B3,IF(C3="OFFICER",10%*B3,5%*B3))))

(B3>15000,10%*B3,IF(B3>10000,5%*B3,200)))

*K3,IF(C3="MANAGER",15%*K3,IF(C3="ASSISTANT MANAGER",10%*K3,IF(C3="OFFICER",

*K3,IF(C3="MANAGER",15%*K3,IF(C3="ASSISTANT MANAGER",10%*K3,IF(C3="OFFICER",

IF(M3>350000,40%*B3,IF(M3>200000,20%*B3,IF(AND(M30),10%*B3,0))))

ragDown Arrow of BASIC, Go into Custom and Select ">" type 20000 and Press OK. (RA

CONVEYANCE SPECIAL ALL.#1 T_ALL.S SPECIAL ALL.#23750 6250 23750 5000

3750 6250 23750 5000

BIG P

Find Value of Cell A5 in column#2 and #3 by using Vlookup Function

The VLOOKUP range is A2:C14


48/76

BASIC

25,000.00Rs. 10000

25,000.00Rs. I44=EMP_NAME Cell Address 10000

15,000.00Rs. 5000

10,000.00Rs. 5000

10,000.00Rs. 5000

6,000.00Rs. 5000

4,000.00Rs. 5000

15,000.00Rs. 5000

10,000.00Rs. 500010,000.00Rs. 5000

6,000.00Rs. 5000

4,000.00Rs. 5000

Sort the Chart Range

Data in descending order

Highlight all Data by Mouse

Go into Data Menu

Click Sort

Select Emp_Name in Sortby

Click Descending Button

and then Click OK

Score

100

45

56

78

594.9166667

24.39091361

=VLOOKUP(A5,A2:C14,3,TRUE)

15000 =VLOOKUP(A5,A2:C14,2,TRUE)

=IF(OR(I44="A",I44="B"),10000,5000)

Description

ondition Range Use IF Condition with OR

and "Stdev"

ASSISTANT MANAGER

B

C A

B

C

D

E

F

G

H

IJ

K

L

If the value of I44equals to "A" OR "B"then assign 10000 in

H44otherwise put 5000 in

H44


49/76

_ LL. _ _ L

3750 28750 53,750.00Rs.3750 28750 53,750.00Rs.

750 15750 30,750.00Rs.

200 10000 20,000.00Rs.

200 10000 20,000.00Rs.

200 4500 10,500.00Rs.

200 3000 7,000.00Rs.

750 15750 30,750.00Rs.

200 10000 20,000.00Rs.

200 10000 20,000.00Rs.

200 4500 10,500.00Rs.

200 3000 7,000.00Rs.

21000 148000 288000

Rate 10%

Years 2

Deposits 1,000

PV= 1736

FV= 2,100

5%*K3,200)))) Rate 0.92%

Month 300

5%*K3,200)))) Loan 100000

PMT= 983.01

=PV(Rate, Number

=FV(Rate, Number

GE IS A2:O14) =PMT(Rate, Numb

I_TAX ALL.S_GT G_SALARY3750 28750 53750

3750 28750 53750

YROLL SHEET

Calculate Present Value and Future Val

Calculate Payment(PMT)


50/76

EMP_NAME G_SALARY

A 53750

B 53750

C 30750

D 20000

E 20000

F 10500

G 7000

H 30750

I 20000J 20000

K 10500

L 7000

EMP_NAME G_SALARY

L 7000

K 10500

J 20000

I 20000

H 30750

G 7000

F 10500

E 20000

D 20000

C 30750

B 53750

A 53750

Chart & IF Condition Range

SelGo into Insert

IcoTh

Select CharNow

Then Give the Cin

Select "A

0

10000

20000

30000

40000

50000

60000


51/76

_ U H _ H _ U

10,750.00 650,000.00 13,750.0010,750.00 600,000.00 13,750.00

3,075.00 500,000.00 6,000.00

1,000.00 400,000.00 4,000.00

1,000.00 425,000.00 4,000.00

200.00 350,000.00 1,200.00

200.00 300,000.00 800.00

3,075.00 250,000.00 3,000.00

1,000.00 200,000.00 1,000.00

1,000.00 225,000.00 2,000.00

200.00 -

200.00 150,000.00 400.00

32450 4050000 49900

Sum the Values >2000

Formulas Ans

=PV(K18,K19,K20) 169,000.00Rs.

=FV(K18,K19,K20) Sum the Values


52/76

Create a Bar Chart

Range

Emp-name+G-Salary

Make Chart Easily

ect the Data with Mouse.Menu and click Chart or Click Chartn in Standard Toolbars.Chart window appears.

t Type than click on Next Button.Check your Chart Range.

Click Next.

art Title and use other Chart optionsChart Wizard Window.Click Next.

s Object In" then click Finish.The Chart is Ready.

G_SALARY

PAYROLL

A

B

C

D

E

F

G

H

I

J

K

L

How to

OpenMake a Dat

dFill the req

different func

Refer to thein

If you feel aassign

Create


53/76

_ U

28,250.0028,250.00

9,825.00

5,200.00

5,200.00

1,600.00

1,200.00

6,825.00

2,200.00

3,200.00

400.00

800.00

92950

in G_Salary

formula

=SUMIF(K3:K14,">20000")

in G_Salary

=SUMIF(K3:K14,"


54/76

se this Assignment

worksheet Programbase with the all fields asescribed aboveired fields then apply thetions as explained in detailin this sheet.correct cell Addresses inerting formulas.

ny problem regarding thisent please contact toLab Staff.

d By Nadeem Shah

omputer Lab ICMAP


55/76

WHAT IS THIS?

1 To calculate the investment's internal rate of return after four years:

=IRR(J8:J12) equals -2.12 percent -2%2 To calculate the internal rate of return after five years:

=IRR(J8:J13)equals 8.66 percent3 To calculate the internal rate of return after two years, you need to include a guess:

=IRR(J8:J10-10%) equals -44.35 percent

Income of Year-5

SolutionCost (Cost or Investment must be with (-) Minus Sign

Income of Year-1Income of Year-2

IRRReturns the internal rate of return for a series of cash flows represented by the numbers in values.do not have to be even, as they would be for an annuity. However, the cash flows must occur at regsuch as monthly or annually. The internal rate of return is the interest rate received for an investmen

payments (negative values) and income (positive values) that occur at regular periods.

Examplesuppose you want to start a restaurant us ness. ou est mate t w cost , to start t e us n

net the following income in the first five years: $12,000, $15,000, $18,000, $21,000, and $26,000. J8following values: $-70,000, $12,000, $15,000, $18,000, $21,000 and $26,000, respectively.

Income of Year-3Income of Year-4


56/76

-70000

1200015000180002100026000

-2%

9%

-44%

hese cash flowslar intervals,t consisting of

ss an expect to:J13 contain the


57/76

Food Sales Person Name Cost Each Item Sold

Cheese & Tomato Baker 0.35 17

Tuna &Tomato Zambi 0.35 12

Tomato & Cucumber Cethy 0.35 5Egg & Maranade Armor 0.35 2

Mars Maria 0.22 35

Toffee crisp Diana 0.22 27

Galaxy Maria 0.25 7

Bounty Diana 0.22 4

Monster munch Steven 0.22 8

Fries Lilly 0.22 10

Discos Lilly 0.22 5

Walkers Lilly 0.22 1

Coke John 0.35 20

Lilt Morgan 0.35 17

Sprite John 0.35 9

Fanta Morgan 0.35 2

Sweets Lollypops George 0.01 50

Maximum Value of Item Sold for Sandwiches 5.95

Minimum Value of Item Sold for Chocolates

Average of Item Sold for Drinks

Sandwiches

Chocolates

Crisps

Drinks

Test Paper of MS-Excel

Instructions

(Read Carefully)Total Time (1/2 hr)

Q1. Design the given sheet (5 pts)

Q2. Calculate Total Cost (2 pt.)

" " " "


58/76

. . ,

Q4. Calculate Bonus by using VLOOKUP Function. Tips: [The Rate Table is Given Below use this table an

Q5. Calculate Maximum, Minimum and Average by using Excel Built-in Functions. (3 pts.)

Q6. Filter the Sheet. Tips: [ Show only "Baker's" Records]. (3 pts.)

Q7. Sort the Sheet. Tips: [Sort by "Sales Man Name" for Sendwiches in Ascending order]. (2 pts.)

Q8. Create a Pivote Table Report. Tips: [Use "Sales Man Name" and "Food" at Row area and "Total Cost"

Q9. Create a Pie Chart. Tips: [ Use only "Food" and "Item Sold Field" for Crisps]. (2 pts.)

Q10. Apply Conditional Formatting on Column Stocking Tips: [ If the results is Restock the Font Color shoulshould be Green]. (3 pts)

Q11. Apply Validation Rules. Tips: [ Apply these rules on "Item Sold" Column, the Number > 30 should be IData by using Auditing Tool]. (5 pts).


59/76

Total Cost Stocking Bonus

5.95 restock 3

4.2 stock 2

1.75 stock 10.7 stock kick out

7.7 restock 5

5.94 restock 5

1.75 stock 1

0.88 stock kick out

1.76 stock 1

2.2 stock 2

1.1 stock 1

0.22 stock kick out

7 restock 4

5.95 restock 3

3.15 stock 1

0.7 stock kick out

stock

0.5 restock 5

Item Sod Bonus

0 kick out

5 1.0010 2.00

15 3.00

20 4.00

25 5.0030 10.00

Rate Table for Bonus

" "


60/76

.

d use Item Sold Field for Bonus] (4 pts)

at Data area of Pivot Table]. (4 pts.)

ld be red other wise the Fount Color

nvalid otherwise Valid then Circle Invalid


61/76

City Date Fee Attendance Computer Sold Performance

Lahore 23-Jan-99 10000 1000 167 EXCELLENTKarachi 25-Jan-99 12000 1200 200 EXCELLENTQuetta 27-Jan-99 14000 450 500 GOOD

Jan-Total 36000 2650 867

Islamabad 5-Feb-99 13000 2300 369 EXCELLENTMultan 7-Feb-99 14000 300 568 POOR

Hyderabad 9-Feb-99 11000 1250 23 EXCELLENT

Feb-Total 38000 3850 960

Sialkot 12-Mar-99 12000 490 231 GOODFaisalabad 14-Mar-99 16000 1600 600 EXCELLENTPeshawar 15-Mar-99 13000 200 3 POOR

Mar-Total 41000 2290 834

Tour Grand Total 115000 8790 2661

Q1. Create a worksheet given above the doted line. 5 PTS

Q2. Calculate Totals of FEE, ATTENDANCE and 10 PTSCOMPUTER SOLD for all months and Grand Total.

Q3. Calculate Performance using IF Condition. 10 PTSIF ATTENDANCE is >500 then Performance is "EXCELLENT"

IF ATTENDANCE is >300 then Performance is "GOOD"

ELSE "POOR" (Paste the If Function as a text at the end of the Sheet)

Q4. Calculate following. 5 PTSAverage Fee for the month of Jan at the end of the Sheet

Q5. Create a Bar Graph of Cities against Attendance 5 PTSfor the month of Feb.

Total Points 35

1999 Computer Tour

INSTRUCTIONS

0

500

1000

1500

2000

2500

Attendance


62/76


63/76

City Attendance

Lahore 1000Karachi 1200Quetta 450

Islamabad 2300Multan 300

Hyderabad 1250Sialkot 490

Faisalabad 1600

Peshawar 200

Attendance


64/76

Accountants Tour of London

Name Design Lunch Others B. Fast Drinks

Asim Director 10 15 17 13Nabeel Officer 0 12 20 8Faisal Assistant 15 12 0 14

Nadeem Director 7 14 9 13

Aijaz Director 12 5 6 2Sajjad Officer 13 24 10 6

Sub Total

Q1. Create a Worksheet given above the doted line 5pts

Q2. Calculate Average & Total 5pts

Q3. Calculate Remarks using IF Condition 5pts

Q4. Paste IF Condition as a Text 2ptsQ5. Calculate Sub Totals 3ptsQ6. Calculate Grand Total 5ptsQ8. Calculate Suggestion 5pts

Q.9 Create a Pie Chart of Name & Total 5pts

35

IF TOTAL >40 "GOOD" Else "VGOOD"

Calculate Suggestion using IF Condition with Average

IF Average > 10 "Please Take Care" "OK

Total Points

Tour of London

Grand Total

INSTRUCTIONS

IF TOTAL>50 "BAD"


65/76

J uly 20, 2002 to J anuary 15, 2003

Avg. Total Remarks Suggestion

13.75 55 BAD Please Take Care10 40 VGOOD Ok

10.25 41 GOOD Please Take Care10.75 43 GOOD Please Take Care6.25 25 VGOOD Ok

13.25 53 BAD Please Take Care


66/76

Limit of Sale Comm% Remarks Description1 2 POOR Original Cost

101 4 BAD Life Year

201 6 GOOD Salvage Value301 8 VGOOD

401 10 EXLT501 12 EXLT

Sales Man Sales Comm Remarks Year Straight

KASHIF 120 4 BAD Line

NADEEM 12 2 POOR SLN

AKBAR 190 4 BAD 1 Rs.225.00

FAISAL 308 8 VGOOD 2 Rs.225.00NABEEL 450 10 EXLT 3 Rs.225.00ARIF 130 4 BAD 4 Rs.225.00

Q1

Q2 Calculate Commission by using VLOOKUP (The Rate Table is give above)

Q3 Calculate Remarks USING VLOOKUP (The Rate Table is give above)

Q1 Create the worksheet given above

Q2 Calculate SLN, DDB and SYD

Q3 Paste all Formulas of Sheet1 and Sheet two as a text at the end of the Sheets.

Total Points

SHEET 1 SH

USING VLOOKUP FUNCTION

Rate Table DEPRECIATION COM

Create a column Chart against Salesman and Sales

INSTRUCTIONS FOR SHEET2

INSRUCTIONSINSTRUCTIONS FOR SHEET1

Create the worksheets given above the doted lines

OR


67/76

Calculator1000

4

100

D.Declining Sum of the

Balance Year Digits

DDB SYD

Rs.500.00 Rs.360.00

Rs.250.00 Rs.270.00Rs.125.00 Rs.180.00

Rs.25.00 Rs.90.00

5 pts

5 pts

5 pts

5 pts

10 pts

5 pts

35

ET 2

ARISON


68/76

TEAM NAME MATCH1 MATCH2 MATCH3 TOTAL WON LOST AVG SCORE TOTAL WORLD RANKINGAustralia 235 325 345 905 3 0 302 670 1stBangladesh 120 111 67 298 0 3 99 231 3rdCanada 156 201 189 546 1 2 182 390 3rdEngland 234 167 178 579 2 1 193 412 3rdndia 337 289 290 916 3 0 305 627 1stPakistan 278 321 322 921 3 0 307 643 1stSouth Africa 320 278 290 888 3 0 296 610 3rd

West Indies 221 189 190 600 2 1 200 411 2nd

Total Time Allowed 25 Minutes

Total Marks = 25Q1 Design the given sheet (3pts)Q2 Calculate LOST Matches by using Formula (3pts)Q3 Calculate Average Score by using Excel Function (2pts)

Q4 Calculate the Total of Two Greatest Values from Three Matches by using Proper Excel Formulas (3pts)

Q5 Calculate World Ranking by using IF condition (5pts)Tips:

IF the Team Won All Three Matches & Average Score is Greater than or Equals to 300, Ranking will be 1st

IF the Team Won Only Two Matches & Average Score is Greater than or Equals to 200, Ranking will be 2ndOtherwise Ranking will be 3rd.

Q6 Calculate Remarks by using IF Condition (2pts)Tips:

IF the World Ranking is 3 the team is POOR

If the World Ranking is 2 the team is GOOD

If the World Ranking is 1 the team is EXLT

Q7 Create a Pivot Table Report on a new worksheet (4pts)Use Team Names, Won Matches and Average Score for Pivot Table

Q8 Apply Filter Command "Show EXLT Teams Only" (3pts)

WORLD CUP

ONE DAY CRICKET MATCH REPORT

NSTRUCTIONS


69/76

G4 ==>

H4 ==>

I4 ==>

J4 ==>K4 ==>

=AVERAGE(B4:D4)

=SUM(B4:D4)-MIN(B4:D4)

=IF(AND(F4>2,H4>=300),"1",IF(AND(F4>1,H4>=200),"2","3"))

=IF(J4="3","POOR",IF(J4="2","GOOD","EXLT"))

SOLUTION=3-F4


70/76

NAME BASIC SALARY GRADE SEX LOCATION HOUSE RENT MEDICAL CONV.

Zahida 3000 17 F Gulshan 300 360 600Hamid 2800 16 M Malir 280 336 560Benson 2500 15 M Orangi 250 250 500Ali 4000 19 M Landhi 520 600 800Dawar 4500 20 M Steel Town 900 990 1575Yasmin 4500 20 F Korangi 900 990 1575Ali 4500 20 M Gulbarg 900 990 1575Nomana 3000 17 F F.B. Area 300 360 600Badar 2800 16 M Clifton 280 336 560Ali 3000 17 M Gulshan 300 360 600

Chachar 2500 14 M Nazimabad 200 250 500Bushra 4000 19 F F.B. Area 520 600 800

INSTITUTE OF COST AND MANAGEMENT ACCOUNTAN

Condition:

HOUSE RENT: Grade>=15, 10% of Basic, Grade>17, 13% of Basic, Grade>19, 2of Basic.

MEDICAL: Grade>=15, 12% of Basic, Grade>17, 15% of Basic, Grade>19, 22

of Basic.

CONV. Grade>=15 and Sex=F and Location is either Korangi, Landhi, SteOtherwise 20% of Basic, Grade>19, 35% of Basic.

I. TAX: 5% of Basic

STATUS: Grade>=19, Manager, Grade>=17, Dep. Manager, else Clerk.

___________________________________________________________________

INSTRUCTIONS:

- Design the Sheet using the above Condition.- Develop a Pie Chart between Name and Gross Salary.- Paste All Formulas as Text at the very end of this Sheet.


71/76

FOR H. RENT =IF(C6>19,B6*20%,IF(C6>17,B6*13%,IF(C6>=15,B6*10%,B6*8%)))FOR MEDICAL =IF(C6>19,B6*22%,IF(C6>17,B6*15%,IF(C6>=15,B6*12%,B6*10%)))

FOR CONV. =IF(C6>19,B6*35%,IF(AND(C6>=15,D6="F",OR(E6="Korangi",E6="Landhi",E6=I. TAX =B6*5%

NET =J6-I6STATUS =IF(C6>=19,"MANAGER",IF(C6>=17,"DEP. MANAGER","CLERK"))

SOLUTION


72/76

I. TAX GROSS NET STATUS

150 4260 4110 Dep.Manager 140 3976 3836 Clerk125 3500 3375 Clerk200 5920 5720 Manager 225 7965 7740 Manager 225 7965 7740 Manager 225 7965 7740 Manager 150 4260 4110 Dep.Manager 140 3976 3836 Clerk150 4260 4110 Dep.Manager

125 3450 3325 Clerk200 5920 5720 Manager

TS OF PAKISTAN

PAYROLL SHEET

% of Basic Otherwise 8%

% of Basic Otherwise 10%

l Town, 30% of Basic

_________________


73/76

Steel Town")),B6*30%,B6*20%))


74/76


75/76

total everage

Zahid 35 24 24 83 27.66667Akber 56 78 45 179 59.66667

Nida 12 13 23 48 16Moon 56 76 34 166 55.33333Noor 26 75 87 188 62.66667Faisal 53 56 78 187 62.33333Benson 65 67 89 221 73.66667maximumMinimum

Roll# Test1 Test2 Test3 Test4 Test5 TOTAL

101 45 67 87 86 35102 23 76 68 54 76103 34 67 78 75 82104 94 89 79 90 88105 23 56 74 33 67

106 36 78 83 93 77107 38 47 46 59 34108 56 67 78 87 45109 88 89 99 98 89110 12 14 34 14 24


76/76

rks Obtain Per% Remarks Grade

learn exxel formulae

Documents