Excel formula trick

 Excel Formula Creations

 

Sum =Sum(A1:A9)

Subtract =A2-A1

Multiply =A2*A3*A4

Percentage =A9/800*100

Count =Count(A1:A9)

Average = Average(c2:n2)

Min =min(c2:n2)

Max =max(c2:n2)

Rank =rank(p2,$p$2:$j$12,0  or 10)

press F4 to Lock 

G.P.A =(t2/600)*150

Division

=if(i2>=90,”Distinction”,if(i2>=60,

”First Division”,if(i2>=50,”Second

Division”,if(i2>=40,”Third Division”,”Fail/Pass”))))

=if(i2>=90,”Distinction”,if(i2>=60,

”First Division”,if(i2>=50,”Second

Division”,”Third Division”)))

Grade

=If(i2>=90,”A+”,If(i2>=80,”A”,If(i2>=70,”B+”,

If(i2>=60,”B”,If(i2>=50,”C+”,If(i2>=40,”C”,

If(i2>=35,”D+”,”NG”)))))))

Pass/Fail

=if(countif(c2:g2,”>=33”)=5,”Pass”,

if(c2:g2,”<=33”)=3,

”Fail”,”Compartment”

=if min(d4:d7)>=35,”pass”,”fail”