Excel Formula

AGE CALCULATION BIRTH DATE:

28-Oct-63

Years lived : and the months : and the days :

BIRTH DATE:

#NAME? #NAME? #NAME?

You can put this all together in one calculation, which creates a text version. 28-Oct-63 #NAME?

Another way to calculate age This method gives you an age which may potentially have decimal places representing the months. If the age is 20.5, the .5 represents 6 months.

BIRTH DATE: Age is :

28-Oct-63 45.74

tes a text version.

Calculate Total, Average, Maximum, Minimum and Remarks by using Excel Functions. If you feel problem to solve see the help that is given below. S# 1 2 3 4 5 6 7

Name Zahid Akber Nida Moon Noor Faisal Benson

Test1 35 56 12 56 26 53 65

Test2 24 78 13 76 75 56 67

65

Test3 24 45 23 34 87 78 89

Total 83 179 48 166 188 187 221

13

HELP WITH INSTRUCTIONS 1 Calculate Total =SUM(FIRST RANGE:LAST RANGE) 2 Calculate AVERAGE =AVERAGE(FIRSTRANGE:LASTRANGE)

3 Calculate Maximum Value of Test1 and Minimum Value of Test2 =MAX(FIRSTRANGE:LASTRANGE) =MIN(FIRSTRANGE:LASTRANGE)

Type Type Type Type

in in in in

Cell Cell Cell Cell

SOLUTION C10=MIN(D3:D9) C10=MAX(C3:C9) G3 and Copy the formula =AVERAGE(C3:E3) H25 and Copy the formula=IF(F25>100,"GOOD","POOR")

AVG 41.5 89.5 24 83 94 93.5 110.5

Remarks POOR GOOD POOR GOOD GOOD GOOD GOOD

Nested "IF" Conditions. GRADE SHEET Student Name Sana Khan M. Ali Kamran Saeed Nadia Barlas Nadeem Syed Arif Mustafa David Furqan Haider M. Ali Majid Bilal

Type in Cell I3 Type in Cell J3 Type in Cell K3 Type in Cell L3

Roll# 101 102 103 104 105 106 107 108 109 110

Test1 45 23 34 94 45 36 38 56 88 12

Test2 67 76 67 89 56 78 47 67 89 14

Test3 87 68 78 79 74 83 46 78 99 34

Test4 86 85 75 90 45 93 59 87 98 14

Test5 35 76 82 88 67 77 34 45 89 24

Soluttion =SUM(C55:G55) =I55/H55*100 =IF(J55>=80,"EXLT",IF(J55>=70,"V.GOOD",IF(J55>=60,"GOOD","BAD"))) =IF(J55>=80,"A1",IF(J55>=70,"A",IF(J55>=60,"B","FAIL")))

HEET TOTAL 500 500 500 500 500 500 500 500 500 500

Marks Obtained 320 328 336 440 287 367 224 333 463 98

Per% 64 65.6 67.2 88 57.4 73.4 44.8 66.6 92.6 19.6

Remarks GOOD GOOD GOOD EXLT BAD V.GOOD BAD GOOD EXLT BAD

Grade B B B A1 C A FAIL B A1 FALSE

WEEKDAY DATE 28-Oct-63 14-Mar-93 5-Jul-86 6-Jul-86 7-Jul-86

Weekday

2 1 7 1 2

Syntax

=WEEKDAY(Date,Type) Type : This is used to indicate the week day numbering system. 1 : will set Sunday as 1 through to Saturday as 7 2 : will set Monday as 1 through to Sunday as 7. 3 : will set Monday as 0 through to Sunday as 6. If no number is specified, Excel will use 1.

NO. 1 2 3 4 5 6 7

Weekday SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY

PI π 3.14159265358979

Radius 2 7

=PI()

Area 12.57 153.94

FACT NUMBER 3 10 8 4 7 10

FACTORIAL 6 3628800 40320 24 5040 3628800

What Does It Do ?

This function calculates the factorial of a number. The factorial is calculated as 1*2*3*4..etc. The factorial of 5 is calculated as 1*2*3*4*5, which results in 120. Decimal fractions of the number are ignored. Syntax

=FACT(Number)

NAME A A A A A A B B B B B C C D F N Q Y Z

BASIC 3000 2300 3400 4000 5000 2200 2200 2400 5600 6000 7000 5000 3000 3500 3400 2500 2000 4000 2500

SORTING AND FILTER SALE1 SALE2 123 45 230 56 340 56 356 67 367 78 100 300 145 234 150 100 456 345 245 666 346 890 45 67 56 78 365 456 56 67 200 300 150 250 240 200 450 250

Tips STEPS FOR SORTING STEP# 1

Select the above sheet from Cell# A2:F21

STEP# 2

Go into Data Menu and select Sort command You will see a Sorting Window now select Ascending or Descending order and select the Column# by using sort by option and then by option.

STEPS FOR FILTERS STEP# 1

Select the above sheet from Cell# A2:F21

STEP# 2

Go into Data Menu and select Filter > Auto Filter Command You will see the drop down buttons with every field You can use these buttons to Filter your data

ILTER

ips

order and select the

SALE3 67 345 360 89 98 500 50 400 67 78 456 678 89 345 89 250 400 124 400

TOT.SALE 235 631 756 512 543 900 429 650 868 989 1692 790 223 1166 212 750 800 564 1100

Grade Book

VLOOKUP FUNCTION Student Name

Haider Ali Babar Zahid Faisal Ameen Rasool Qadir Yahya Alia

ID# 6 3 4 1 7 2 5 8 9 10

Test1 67 56 78 98 34 67 24 67 23 45

Test2 56 57 89 99 23 78 34 84 46 67

Test3 89 78 88 91 22 89 34 66 57 45

Test4 68 46 90 95 12 67 23 77 87 78

Grading Criteria 0 40 50 60 70 80

Table_Array is the complete table of information

Solution With Instructions STEP# 1 First you have to Calculate Per% by using this formula in Cell# J4 Marks Obtain Divided by Total Marks Multiply by100 '=I159/H159*100 STEP# 2 After Calculating Per% we can Find out the Grades by using VLOOKUP Worksheet Function

Type this formula in Cell# K4 '=IF(J159>=80,"A+",IF(J159>=70,"A",IF(J159>=60,"B",IF(J159>=50,"C"

Lookup_Value

Book Test5 80 47 87 90 67 97 34 86 90 88

Total 500 500 500 500 500 500 500 500 500 500

M. Obtain 360 284 432 473 158 398 149 380 303 323

Per% 72 56.8 86.4 94.6 31.6 79.6 29.8 76 60.6 64.6

Grade A C A+ A+ FAIL A FAIL A B B

Grading Criteria FAIL D C B A A+

Col_Index is the Column# of Table_Array

on With Instructions

59>=60,"B",IF(J159>=50,"C",IF(J159>=40,"D",IF(J159>=0,"FAIL"))))))

DDB, SLN, SYD METHODS Cost of Computer Salvage Value Life

20000 5000 10

YEARLY DEPRECIATION Period in Years 1 2 3 4 5 6 7 8 9 10

Double Declining $4,000.00 $3,200.00

Straight Line Rs.1,500.00

$1,638.40

$0.00

SOLUTION Double Declining Straight Line Sum of Years Digit

Rs.6,000.00 Rs.2,500.00 Rs.4,545.45

'=DDB(B192,B193,B194,A198) '=SLN(B192,B193,B194) '=SYD(B192,B193,B194,A198)

DS

ON Sum of Years Digit Rs.2,727.27

,B193,B194,A198) ,B193,B194) ,B193,B194,A198)

Subtotals Person Name

Type

Faisal Rufi Nadeem Faisal Babar Nadeem Rufi Ayjaz Ayjaz Babar Nadeem Rufi Zubair Faisal Kashif Nadeem Rufi Ayjaz Nadeem Zubair

Ribbon Ribbon Dimm Scanner Printer Printer Printer TV TV TV TV VCR VCR Computer Computer Computer Computer Car Car Car

Unit Sold 4 9 10 2 1 5 5 4 3 1 6 4 2 3 5 7 6 1 2 1

Unit Price 300 300 500 3000 6000 6000 6000 10000 10000 10000 10000 12000 12000 50000 50000 50000 50000 200000 200000 200000

Tips with Instructions STEP# 1 STEP# 2 STEP# 3 STEP# 4 STEP# 5 STEP# 6

Sort the list by the column for which you want to Calculate subtotals. Select data from A2 to E22 On the Data menu, click Subtotals. You will see a new window In the At each change in box, click the column that contains the group for which you want subtotals. In the Use function box, click the function you want to use to calculate the subtotals. In the Add subtotal to box, select the check boxes for the columns that contain the values for which you want to subtotals.

Total Price 1200 2700 5000 6000 6000 30000 30000 40000 30000 10000 60000 48000 24000 150000 250000 350000 300000 200000 400000 200000

you want to

ck boxes for

PIVOT TABLE REPORT NAME ALI BABAR SAFDAR ZIA BILL ALI SILVESTER NOMAN BABAR YOUSUF AZIZ AKRAM ALI RAO MALIK AFSAR RAEES RASHEED ZIA

BASIC 5000 4000 3400 3000 2300 2200 7000 6000 5600 2400 2200 5000 3000 3500 3400 2500 2000 4000 2500

REGION EAST WEST EAST NORTH SOUTH EAST WEST SOUTH NORTH EAST WEST EAST EAST SOUTH NORTH WEST SOUTH EAST SOUTH

SALE1 367 356 340 135 230 100 346 245 456 150 145 45 56 365 56 200 150 240 450

SALE2 78 67 56 45 56 300 890 666 345 100 234 67 78 456 67 300 250 200 250

Tips with Instructions STEP# STEP# STEP# STEP# STEP#

1 2 3 4 5

First select the above sheet from Cell A2 to G21 Go into Data Menu and select the command Pivot Table and Pivot Chart Report You will see the Pivot Table Wizard now Press the next button Again a small window appears and ask for Data Range that you have selected b Now Wizard will ask you where do you want to put the Pivot Table Report

SALE3 98 89 360 67 345 500 456 78 67 400 50 678 89 345 89 250 400 124 400

TOT.SALE 543 512 756 247 631 900 1692 989 868 650 429 790 223 1166 212 750 800 564 1100

uctions

d Pivot Chart Report

you have selected before, Press Next t Table Report

COUNT FUNCTION Test1 Test2 Nill Nill 56 78 99 13 56 99 26 99 89 99 Nill 67

Name Zahid Akber Nida Moon Noor Faisal Benson

Test3 89 45 23 99 87 78 89

5

Solution =COUNT(B3:B9) =COUNT(C3:C9) =COUNT(D3:D9)

=5 =6 =7

Type in Cell B10 Type in Cell C10 Type in Cell D10

CHARTS SHEET 1

Pakistan VS Australia 1st Match

2nd Match

234 235

Pakistan Australia

301 256

3rd Match

4th Match

298 289

156 158

Select Sheet # 1 from A4 TO F6, Press F11 Function Key the Quick Chart Will be created on a new sheet.

SHEET2 NAME

TOTAL SALE

SHAH ALI KAMAL SONIA SANA ZIA YASEEN QAMAR ALAM

10000 8900 3450 6789 12345 13456 10988 6789 45677

Select Sheet # 2 from A14 TO B23, Click on Chart Wizard Icon and follow all instructions.

5th Match

ed on a new sheet.

213 214