Question

Write formula ( Not answer) for the operations ( for a - e) based on the spreadsheet given below along with the relevant cell address:

A B C D E F G H

Sr. no Name Gender Science Maths Computer Total Average

1 1 Max M 34 75 39 ? ?

2 2 Swati F 47 29 33 ? ?

3 3 Shruti F 83 51 42 ? ?

4 4 Ram M 63 94 68 ? ?

5 5 Neelu F 51 90 45 ? ?

6

7 Max ?

8 Total ?

9 Average Marks of Males M ?

10 Average Marks of females F ?

A. to calculate the Total Marks as sum of science, Maths and computers for each student and display them in column G.

B. To calculate the average marks for each student and display them in column H.

C. To calculate the highest marks in Computers and display it in cell F7.

D. To calculate the total number of students appearing in science and display it in cell D8.

E. To calculate the average marks for Male students in cell H9 and for female students in cell H10.

Answer 1

$A.$

$cell \ G1\ formula\ =SUM(D1:F1)$

$cell \ G2\ formula\ =SUM(D2:F2)$

$cell \ G3\ formula\ =SUM(D3:F4)$

$cell \ G4\ formula\ =SUM(D4:F4)$

$cell \ G5\ formula\ =SUM(D5:F5)$

$B.$

$cell \ H1\ formula\ =AVERAGE(D1:F1)\newline cell \ H2\ formula\ =AVERAGE(D2:F2)\newline cell \ H3\ formula\ =AVERAGE(D3:F3)\newline cell \ H4\ formula\ =AVERAGE(D4:F4)\newline cell \ H5\ formula\ =AVERAGE(D5:F5)\newline$

$C.$

$cell \ F7\ formula\ =MAX(F1:F5)$

$D.$

$cell \ D8\ formula\ =COUNT(D1:D5)$

$E.$

$cell \ H9\ formula\ =AVERAGEIF(C1:C5;"M";D1:F5)$

$cell \ H10\ formula\ =AVERAGEIF(C1:C5;"F";D1:F5)$