Useful Functions of Excel (Formulae)

Percentage
=Total cell / Number of subject
Remarks (Pass/Fail)
=if(marks>=40,if(marks>=40,if(marks>=40,”pass”,”fail”)))
Division
=if(and(remarks=”pass”,percentage>=40,percentage<60),”3rd”,if(and(remarks=”pass”,per>=60,per<70),”2nd”,if(and(remarks=”pass”,per>=70,per<80),”1st”,if(and(remarks=”pass”,per>=80),”dis”,”no division”))))
Work with salary sheet
Service Status
=if(working year >=3,”p”,”t”)
Salary
=if(post cell =”A”,5000, if(post cell=”b”,4000,if(post cell =”c”,3000,2000))
Grade Rate
=if(post cell =”A”,500, if(post cell=”b”,400,if(post cell =”c”,300,200))
Number of grade
=if(and(w.y>=3,wy<=5),1,if(and(w.y>=6,wy<=10),2,if(and(w.y>=11,w.y<=15),3,if(and(w.y>=16),4,0))
Provident fund
=if(service status=”p”,basic*10%,0)
Tax
=if(and(marital Status=”single”,Gendel=”male”),basic*26%, if(and(marital stauts=”married”,Gendel=”male”),”basic*20%,if(and(marital status=”single”,gendel=”female”),basic salary*19%,basic salary *12%)
Max
Display maximum target cell value
=max(cell address .. cell address)
Min
Display minimum target cell value
=min(cell address .. cell address)
Average
Display average value
=average(cell address .. cell address)
Count
Display total number of cell
=count(cell address .. cell address)
countif
=Logical condition counting
= countif(condition,cell range)

=today()
=Now()
=roman(value)