1.

BASIC FORMULAS

The NOW() function returns the current date and

1 now() now() time based on your computer’s system clock. 03-01-2025 11:24
2 Min() min(78,23,45,67,90) Returns the smallest value 23
3 Max() max(78,23,45,67,90) Returns the highest value 90
It is designed to compute the square root of a given
4 Sqrt(number) sqrt(2) number. 1.414213562
Count the number of cells that contain numbers in a
5 count count(2,13,4,67,78,9,80,7) specified range 8
The TRIM() function removes all extra spaces from
a text string, leaving only single spaces between
6 Trim() Trim(clickup sheets) words. Trim(Clickup sheets)
This formula raises a number to the power of
7 Power() Power(2,3) another number (exponentiation). 8
This rounds a number up to the nearest multiple of a
8 Ceiling() Ceiling(3.46,1) specified significance. 4
This rounds a number down to the nearest multiple
9 Floor() Floor(3.46,1) of a specified significance. 3
Both CONCATENATE() and CONCAT() are used
10 Concat() Concatenate(john, ”,”,ajay) to combine multiple text strings into one. John ajay
The LEFT() function extracts a specified number of
11 Left() Left(731562894,3) characters from the start (left side) of a text string. 731
The RIGHT() function extracts a specified number
of characters from the end (right side) of a text
12 Right() Right(731562894,3) string. 894
The MID() function extracts a specified number of
characters from the middle of a text string, starting
13 Mid() mid(731562894,3,2) at a specified position. 15

1
 The LEN() function returns the number of
14 Len() Len(hello) characters in a text string, including spaces. 5
The UPPER() function converts all letters in a text
15 Upper() Upper(hello) string to uppercase. HELLO
The LOWER() function converts all letters in a text
16 Lower() Lower(HELLO) string to lowercase. hello

2. PAYBILL PREPARATION

Type the Employee details related with given data

GVS PRIVATE LTD

EMP. EMPLOYEE BASIC GROSS
SNO NO. NAME DESIGNATION SALARY HRA DA PF LIC PAY NET PAY
1 E1001 AJAY.K MANAGER 65000 0.83 0.83 64.2 64.2 65001.66 64873.26
2 E1002 SUNIL.R SUPERVISOR 44000 0.72 0.71 38.7 38.7 44001.43 43924.03
3 E1003 RAVI.F LABOUR 20000 0.35 0.31 24.3 24.3 20000.66 19952.06
4 E1004 GOMATHI.S LABOUR 20000 0.35 0.31 24.3 24.3 20000.66 19952.06
5 E1005 VIVEK.W LABOUR 20000 0.35 0.31 24.3 24.3 20000.66 19952.06

GROSS PAY = BASIC SALARY+HRA+DA

NETPAY=GROSS PAY – (PF+LIC)

2
 3. STUDENT MARKLIST

Type the Student details related with given data

STUDENT
SNO NAME REGNO TAMIL ENGLISH MAJOR ALLIED EVS TOTAL AVERAGE RESULT GRADE
1 ARUN.G E25002 76 85 90 72 67 390 78 PASS DIS
2 NIVETHA.S E25003 64 72 82 69 45 332 66.4 PASS FIR
3 RAJA.D E25004 91 89 83 64 23 350 70 FAIL *
4 REKHA.D E25005 56 52 69 58 57 292 58.4 PASS SEC
5 VINO.Q E25001 44 44 41 40 78 247 49.4 PASS THI

Total :

Adds all the numbers in a specified range or set of values. It works only on cells with numerical values.

= sum(First cell: Last cell) (i.e) =sum(d3:h3) (or)

= first cell + second cell + third cell +fourth cell +fifth cell (i.e) Total=sum(d3+e3+f3+g3+h3)

Average:

=Total/Number of marks (i.e) Average=i3/5

calculates the average (mean) of the numbers in a specified range or set of values.

3
Result :

= if (and(mark1>=40,mark2>=40,mark3>=40,mark4>=40,mark5>=40),”PASS”,”FAIL”)

(i.e) Result=if(and(d3>=40,e3>=40,f3>=40,g3>=40,h3>=40),”PASS”,”FAIL”)

Grade:

=if(and(result=”pass”),if(and(average>=75),”distinction”,if(and(average>=60),”first”,if(and(average>=50),”second”,if(a
nd(average>=40),”third”)))),”*******”)

(i.e)
Grade=if(and(k3=”pass”),if(and(j3>=75),”distinction”,if(and(j3>=60),”first”,if(and(j3>=50),”second”,if(and(j3>=40),”t
hird”)))),”******”)

It is mainly nested IF that checks various conditions and returns a specific Grade if the conditions are met.

4
4. EB- BILL PREPARATION

Type the Consumer details related with given data

CONSUMER CONSUMER PREVIOUS CURRENT UNITS RATE PER NET
ID NAME READING READING CONSUMED UNIT AMOUNT
CF0011 Karan 926 950 24 2.5 60
CF0012 Roopa 870 900 30 2.5 75
CF0013 Ramalingam 1000 1300 300 6.35 1905
CF0014 Pandiyan 450 560 110 5 550
CF0015 Kumar 450 500 50 2.5 125

UNIT COST
0-100 2.5
100-250 5
250-500 6.35
> 500 7.1

UNIT CONSUMED = CURRENT READING – PREVIOUS READING

NET AMOUNT = UNITS CONSUMED * RATE PER UNIT

(i.e) UNIT CONSUMED = D3-C3

NET AMOUNT = E3*F3

5
5. SHOPPING LIST

Type the Product details related with given data

[Link] PRODUCT NAME QUANTITY PRODUCT PRICE AMOUNT DISCOUNT

1 PEN 12 15 180 180
2 BISCUIT 5 20 100 100
3 GHEE 2 300 600 300
4 OIL 4 250 1000 500
5 RICE 5 70 350 350
NET AMOUNT 1430

AMOUNT=QUANTITY * PRODUCT PRICE

DISCOUNT= IF(AND(AMOUNT>=500),(AMOUNT/2),AMOUNT)

(i.e) DISCOUNT =IF(AND(F4>=500),(F4/2),F4)

6
6. FILTER OPTION

Type the student details related with given data

SNO STUDENT NAME GENDER DEPT NAME MEDIUM COMMUNITY SCHOLARSHIP

1 AKASH.J MALE ECONOMICS TAMIL BC TP
2 ANU.F FEMALE ECONOMICS ENGLISH MBC PP
3 BALU.D MALE ECONOMICS ENGLISH SC TP
4 BANU.R FEMALE ECONOMICS TAMIL SCA PP
5 CHANDRU.K MALE ECONOMICS TAMIL ST TP
6 DEVI.S FEMALE ECONOMICS ENGLISH BC PP
7 DHINAKARAN.M MALE ECONOMICS TAMIL MBC TP
8 DHIVYA.S FEMALE ECONOMICS ENGLISH SC PP
9 ELAKKIYA.P FEMALE ECONOMICS TAMIL SCA PP
10 FATHIMA.K FEMALE ECONOMICS ENGLISH BCM PP
11 FAYAZ.S MALE ECONOMICS ENGLISH BCM TP
12 FRANCIS.K MALE ECONOMICS TAMIL BC CHRISTIAN TP
13 GANESH.D MALE ECONOMICS ENGLISH SC TP
14 GANGA.R FEMALE ECONOMICS TAMIL SCA PP
15 HARI.P MALE ECONOMICS TAMIL ST TP
16 HARINI.J FEMALE ECONOMICS ENGLISH BC PP
17 JANAGAN.T MALE ECONOMICS ENGLISH MBC TP
18 KANIRAJAN.G MALE ECONOMICS TAMIL SC TP
19 MANIMARAN.H MALE ECONOMICS ENGLISH SCA TP
20 MANIMEGALAI.S FEMALE ECONOMICS TAMIL ST PP
21 NANDHINI.G FEMALE ECONOMICS TAMIL BC PP
22 PRAVEEN.D MALE ECONOMICS ENGLISH MBC TP
23 REENA.H FEMALE ECONOMICS TAMIL SC PP
24 RITHISH.G MALE ECONOMICS TAMIL SCA TP
25 SAKSHI.G FEMALE ECONOMICS ENGLISH ST PP

7
 26 SUNIL.B MALE ECONOMICS TAMIL BC TP
27 SUREKHA.E FEMALE ECONOMICS TAMIL MBC PP
28 THIYAGU.R MALE ECONOMICS ENGLISH SC TP
29 VINOTH.A MALE ECONOMICS TAMIL SCA TP
30 VIVEK.R MALE ECONOMICS TAMIL ST TP
31 YUVARAJ.L MALE ECONOMICS ENGLISH BC TP

SELECT THE TABLE -> HOME -> EDITING -> SORT & FILTER-> FILTER

(i) In a table -> choose any gender (MALE / FEMALE)

8
(ii) In a table -> choose any medium (ENGLISH / TAMIL)

9
(iii) In a table -> choose any community (SC,SCA,ST,BC,BCM,MBC)

10
(iv) In a table -> choose any scholarship (PP/TP)

11
7. PREPARE A CHART

CREATE A STUDENT DETAILS RELATED WITH GIVEN DATA

STUDENT VALUE
SNO NAME REGNO TAMIL ENGLISH MAJOR ALLIED EDUCATION TOTAL AVERAGE
1 ARUN.G E25002 76 85 90 72 67 390 78
2 NIVETHA.S E25003 64 72 82 69 45 332 66.4
3 RAJA.D E25004 91 89 83 64 23 350 70
4 REKHA.D E25005 56 52 69 58 57 292 58.4
5 VINOTHA.Q E25001 44 44 41 40 78 247 49.4

12
13