Office-Excel

  
  Question-1
Fill up the range D3:F22 with random integer numbers

ranging from 7 to 163,  using rand() function.

 
  Answer-1
  • RAND() function gives a number (at random) between 0 and 1.
  • The number could be equal to zero, but it will always be less than one.
  • Refreshing or recalculating will generate a new number.
  • For refreshing press F9

Examples

1

 To generate a random integer number between 0 and 89, the eqation will be  RAND()*89
2 To generate a random integer number between 0 and 89, the eqation will be = ROUND(RAND()*89,0)

3

 To generate a random integer number between 7 and 163

Min=7; Max=163   Difference=163-7=156

so the required equation is =7+ROUND(RAND()*156,0)

Please note that the RAND()*156 generates decimal numbers from 0 to 156

and

the ROUND() function converts these decimal numbers to integer numbers (between 0 and 156)
 
     
  Question-2
Marks of ten students are given below. Write the correct equations in cells B12 to B15
          A B C
1 Reg.No     

Marks

           
2 12346 32  
3 12347 64  
4 12348 75
5 12349 48  
6 12350 40  
7 12351 96  
8 12352 42  
9 12353 58  
10 12354 63  
11 12355 40  
12 Average    
13 Maximum    
14 Minimum    
15 No. of  40s  
16                                                          
 

 

Question-2 - Answer
Correct Equation are given below
=AVERAGE(B2:B11)
=MAX(B2:B11)
=MIN(B2:B11)
=COUNTIF(B2:B11,40)
 
     
  Question-3

Automatically change the color of cells (B2:B11) to Red if the Mark in the cell is below 40.

  Answer-3 Hint: Use Menu Format.

and then continue as
> Conditional Formatting>condition1>Cell Value Is >less than>40>Format>color and then select RED
       
  Question-4

Write the Excel equations for the following?

  1. If E4 is equal to or more than 40, then "Passed". Otherwise "Failed".
     
  2. If E4 is equal to or more than 40 and D4 is equal to or more than 40, then "Passed"; otherwise "Failed".
     
  3. If E4 is equal to or more than 40 and D4 is equal to or more than 40  and E4 is equal to or more than 40, then "Passed"; otherwise "Failed".
 
  Answer-4
1.  =IF(E4>=40,"Passed","Failed")
2.  =IF(D4>=40,IF(E4>=40,"Passed","Failed"),"Failed")
3.  =IF(C4>=40,IF(D4>=40,IF(E4>=40,"Passed","Failed"),"Failed"),"Failed")
It is easier to start with the inner IF() and work outwards. In this case go in RED, GREEN, BLUE sequence.
 
 
       
  Question-5 How to protect certain cells from being changed by others?  
  Answer-5 This can be done by Protection

To Protect cells, first Lock the cells, and then Protect the Sheet.

  • Please note that only locked cells will be protected, when you protect a sheet.
  • Also note that you lock the CELL and protect the SHEET

Detailed Steps

First remove Locked state of cells where you want to permit others to enter values. (Please note that all cells are locked initially).  using Format menu>Cell>Protection> and unchek 'Locked'.

The form Tools menu>Protection>Protect Sheet and then uncheck 'Select Locked Cells' and Protect.