AGE CALCULATION

BIRTH DATE: 28-Oct-63

Years lived : 59and the months : 5and the days : 11

You can put this all together in one calculation, which creates a text version.BIRTH DATE: 28-Oct-63

AGE IS 59 YEARS , 5 MONTHS AND 11 DAYS

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: 28-Oct-63

Age is : 59.4442162902122

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# Name Test1 Test2 Test3 Total AVG Remarks

1 Zahid 35 24 24 83 41.5 POOR

2 Akber 56 78 45 179 89.5 GOOD

3 Nida 12 13 23 48 24 POOR

4 Moon 56 76 34 166 83 GOOD

5 Noor 26 75 87 188 94 GOOD

6 Faisal 53 56 78 187 93.5 GOOD

7 Benson 65 67 89 221 110.5 GOOD

65 13

HELP WITH INSTRUCTIONS1 Calculate =SUM(FIRST RANGE:LAST RANGE)

2 Calculat =AVERAGE(FIRSTRANGE:LASTRANGE)

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

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

GRADE SHEETStudent Name Roll# Test1 Test2 Test3 Test4 Test5 TOTAL

Sana Khan 101 45 67 87 86 35 500

M. Ali 102 23 76 68 85 76 500

Kamran Saeed 103 34 67 78 75 82 500

Nadia Barlas 104 94 89 79 90 88 500

Nadeem Syed 105 45 56 74 45 67 500

Arif Mustafa 106 36 78 83 93 77 500

David 107 38 47 46 59 34 500

Furqan Haider 108 56 67 78 87 45 500

M. Ali 109 88 89 99 98 89 500

Majid Bilal 110 12 14 34 14 24 500

SoluttionType in Cell I3 =SUM(C55:G55)Type in Cell J3 =I55/H55*100Type in Cell K3 =IF(J55>=80,"EXLT",IF(J55>=70,"V.GOOD",IF(J55>=60,"GOOD","BAD")))Type in Cell L3 =IF(J55>=80,"A1",IF(J55>=70,"A",IF(J55>=60,"B","FAIL")))

Nested "IF" Conditions.

GRADE SHEETMarks Obtained Per% Remarks Grade

320 64 GOOD B

328 65.6 GOOD B

336 67.2 GOOD B

440 88 EXLT A1

287 57.4 BAD C

367 73.4 V.GOOD A

224 44.8 BAD FAIL

333 66.6 GOOD B

463 92.6 EXLT A1

98 19.6 BAD 0

Soluttion

=IF(J55>=80,"EXLT",IF(J55>=70,"V.GOOD",IF(J55>=60,"GOOD","BAD")))

WEEKDAY

DATE Weekday

28-Oct-63 214-Mar-93 1

5-Jul-86 76-Jul-86 17-Jul-86 2

NO. WeekdaySyntax 1 SUNDAY

=WEEKDAY(Date,Type) 2 MONDAY Type : This is used to indicate the week day numbering system. 3 TUESDAY

1 : will set Sunday as 1 through to Saturday as 7 4 WEDNESDAY 2 : will set Monday as 1 through to Sunday as 7. 5 THURSDAY 3 : will set Monday as 0 through to Sunday as 6. 6 FRIDAY

If no number is specified, Excel will use 1. 7 SATURDAY

PI

p3.14159265358979 =PI()

Radius Area2 12.566370617 153.93804

FACT

NUMBER FACTORIAL3 6

10 36288008 403204 247 5040

10 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)

SORTING AND FILTERNAME BASIC SALE1 SALE2 SALE3 TOT.SALE

A 3000 123 45 67 235A 2300 230 56 345 631A 3400 340 56 360 756A 4000 356 67 89 512A 5000 367 78 98 543A 2200 100 300 500 900B 2200 145 234 50 429B 2400 150 100 400 650B 5600 456 345 67 868B 6000 245 666 78 989B 7000 346 890 456 1692C 5000 45 67 678 790C 3000 56 78 89 223D 3500 365 456 345 1166F 3400 56 67 89 212N 2500 200 300 250 750Q 2000 150 250 400 800Y 4000 240 200 124 564

Z 2500 450 250 400 1100

TipsSTEPS 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

Tips

Grade BookStudent 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

Grading Criteria0 FAIL

40 D

50 C

60 B

70 A

80 A+

Solution With InstructionsSTEP# 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",IF(J159>=40,"D",IF(J159>=0,"FAIL"))))))

VLOOKUP FUNCTION

Table_Array is the complete table of information

Col_Index is the Column# of Table_Array

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

Solution With Instructions

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

Lookup_Value

Col_Index is the Column# of Table_Array

DDB, SLN, SYD METHODS

Cost of Computer 20000Salvage Value 5000Life 10

YEARLY DEPRECIATIONPeriod in Years Double Declining Straight Line Sum of Years Digit

1 $4,000.00 Rs.1,500.00 Rs.2,727.27

2 $3,200.00

3

4

5 $1,638.40

6

7

8

9

10 $0.00

SOLUTIONDouble Declining Rs.6,000.00 '=DDB(B192,B193,B194,A198)

Straight Line Rs.2,500.00 '=SLN(B192,B193,B194)

Sum of Years Digit Rs.4,545.45 '=SYD(B192,B193,B194,A198)

SubtotalsPerson Name Type Unit Sold Unit Price Total Price

Faisal Ribbon 4 300 1200

Rufi Ribbon 9 300 2700

Nadeem Dimm 10 500 5000

Faisal Scanner 2 3000 6000

Babar Printer 1 6000 6000

Nadeem Printer 5 6000 30000

Rufi Printer 5 6000 30000

Ayjaz TV 4 10000 40000

Ayjaz TV 3 10000 30000

Babar TV 1 10000 10000

Nadeem TV 6 10000 60000

Rufi VCR 4 12000 48000

Zubair VCR 2 12000 24000

Faisal Computer 3 50000 150000

Kashif Computer 5 50000 250000

Nadeem Computer 7 50000 350000

Rufi Computer 6 50000 300000

Ayjaz Car 1 200000 200000

Nadeem Car 2 200000 400000

Zubair Car 1 200000 200000

Tips with Instructions

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

STEP# 2

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 to use to calculate the subtotals.

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

Select data from A2 to E22

PIVOT TABLE REPORT NAME BASIC REGION SALE1 SALE2 SALE3

ALI 5000 EAST 367 78 98BABAR 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 89

RAO 3500 SOUTH 365 456 345MALIK 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

Tips with InstructionsSTEP# 1 First select the above sheet from Cell A2 to G21STEP# 2 Go into Data Menu and select the command Pivot Table and Pivot Chart ReportSTEP# 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 have selected before, Press NextSTEP# 5 Now Wizard will ask you where do you want to put the Pivot Table Report

PIVOT TABLE REPORT TOT.SALE

543512756247631900

1692989868650429790223

1166212750800564

1100

Tips with Instructions

Again a small window appears and ask for Data Range that you have selected before, Press Next

COUNT FUNCTION

Name Test1 Test2 Test3Zahid Nill Nill 89Akber 56 78 45Nida 99 13 23Moon 56 99 99Noor 26 99 87Faisal 89 99 78

Benson Nill 67 89

5

Solution=COUNT(B3:B9) = 5 Type in Cell B10

=COUNT(C3:C9) = 6 Type in Cell C10

=COUNT(D3:D9) = 7 Type in Cell D10

CHARTSSHEET 1

Pakistan VS Australia1st Match 2nd Match 3rd Match 4th Match 5th Match

Pakistan 234 301 298 156 213Australia 235 256 289 158 214

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 10000ALI 8900

KAMAL 3450SONIA 6789SANA 12345

ZIA 13456YASEEN 10988QAMAR 6789ALAM 45677

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

CHARTS

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