To compute DA. To compute HRA. To compute PF. To compute GROSS PAY. To compute column total. To find highest gross. To find LEAST gross. To find NO. OF ROWS WHERE BASIC PAY IS LESS THAN 20000 (Excel-2007)

Employee no. , Name and basic pay of 50 employees are entered in cells  A3:A52,B3:B52 &  C3:C52 respectively in a spreadsheet. Explain the procedure to find DA  @26% of basic rounded to its nearest integer in cells D3:D52, HRA @ 30% of basic pay+da in cells E3:E52 ,PF deduction  @ 10% of basic pay rounded to its nearest Rs. 10 in cells F3:F52  and gross pay in cells G3:G52. Also show column totals in cells C53:G53.

Show the value of Highest Gross in cell A55.
Show least value of gross in cell B55.
Show no. of rows where Basic pay is less than Rs. 20000 in cell C55.

To compute DA in column D,

Enter the formula,          

=ROUND(C3*26%,0) in CELL D3. and then press enter key,

using the fill handle of cell D3, copy the formula to cells D4 :D52

By dragging the fill handle up to cell D52.

To compute HRA in column E,

Enter the formula,          

=(C3+D3)*30%  in cell E3 and then press enter key,

using the fill handle of cell E3, copy the formula to cells E4 :E52 by dragging the fill handle up to cell E52.

To compute PF in column F,

 Enter the formula,         

=ROUND(C3*10%,-1) in cell F3 and then press enter key,

using the fill handle of cell F3, copy the formula to cells F4 :F52 by dragging the fill handle up to cell F52.

To compute GROSS PAY in column G,

Enter the formula,          

=C3+D3+E3-F3  in cell G3 and then press enter key,

using the fill handle of cell G3, copy the formula to cells G4 :G52 by dragging the fill handle up to cell G52.

To compute column total in cell C53:G53,

Enter the formula,          

=SUM(C3:C52)  in cell C53 and then press enter key,

using the fill handle of cell C53, copy the formula to cells D53 :G53 by dragging the fill handle up to cell G53.

To find highest gross

Enter the formula,

=MAX(G3:G52)  in cell A55 AND PRESS ENTER KEY.

To find LEAST  gross

Enter the formula,

=MIN(G3:G52)  in cell B55 AND PRESS ENTER KEY.

To find NO. OF ROWS WHERE BASIC PAY IS LESS THAN 20000

Enter the formula,

=COUNTIF(C3:C52,”<20000”)  in cell C55  AND PRESS ENTER KEY.

Related Posts by Categories