XL (bangla)
- 1. ?????????? ?????-?? ?????? ??????? ?? Graphic School Of BangladeshApril 20, 2019 Microsoft Excel 2016all formula of ms excel, formula of ms excel, ms excel, what is ms excel ???? ???? ?????? ?? Microsoft Excel ? ??????? Formula ?????? ??? ??? ?????? ??????? ????? ???? ??? ???? ?? ?? ??????? ????? ??????? ?????? ???? ?? ??????? Function, ??? ?????? ???? ??????? ????? ????? ??? ????? ?????? ???? ???? ?????? ?????? Microsoft Excel ? Formula ?? ????? ??? ??? ???, Formula ??, ?? ?????? ????? ?? ?? ??? ?? ??????? ?????? ????? ?????? ??? ???? ???? ?????? ??? ?? ??????? ????? ??? ?????????? ?????????? STATISTICAL FUNCTION ?????????? ????? ?????? ?????????? ??????? ??? ? ????? ????? ?????? ??????, ??, ???? ?????? ?????, ?? ????? ? ?? ?????? ????? ??? ?? ????? ?????? ?????? ??? ?????? ?????? ??????????? ??? ???? ??? ?????? = ????? ?????? ?????? ? ?????????? ??????? ??? ????? ????- = SUM (List) ????? ? ??????? ?????? ???? ???????? ????? ????? ?????? ?????? ??? ?? ??? ??? = SUM (List) ????? List ??? ????? ?? ???? ???? ???? ???? ?? List ??? ?????? ?? ????? ????????? ???????? ????? ??? (,) ?????? ???? ??? ? ?????? ??????? ??? ???????? C8 ? ????? =SUM (C2:C7) ???? Enter ???? C8 ??? ???? ?????? 3115 ????? =MAX (List) ??????????? ?? ??????? ?????? ???????? ????? ????????? ??? Maximum ?? ??? ?? ????? ??????? ??? ?? ??? ??? ? ?????? ??? ???????? ???? ???? ???- ? ????? ? ?????? =MAX (B2:B5) ???? Enter ???? ????? ?? ????? ????? 89423 ????? ???? =MAX (SAL) ???? Enter ???? ??? ?? ??????? =AVERAGE (List) ??????????? ?? ??????? ?????? ???????? ????? ??????????? ?? ??? ?? ??? ?????????? ??? ???????? ???? ???? ???- ? ????? =AVERAGE (B2:B5) ???? Enter ???? ????? ????? ?????? ?? ?????? ?????
- 2. =MIN (List) ??????????? ?? ??????? ?????? ???????? ????? ????????? ??? ?? ?????? ????? ??? ?? ??? ??? ? ?????? ??? ???????? ???? ???? ???-? ????? =MIN (B2:B5) ????, =MIN (SAL) ???? Enter ???? ????? ????? ?????? ?? ?????? ??????? ?????? ????? =COUNT (List) ??????????? ?? ??????? ?????? ???????? ????? ???? ????? ?? ?? ??? ?? ??? ??? ? ?????? ??? ???????? ???? ???? ???- ? ????? =COUNT (C2:C7) ????, =COUNT (SAL) ???? Enter ???? ????? ???? ???? ????? (?) ??? ?? ?????? ????? =VAR(List) ??????????? ?? ??????? ?????? ???? ????? ????? ?????? ??? ?? ??? ??? ??????? ????? ???? 500, 510, 550, 515, 505, 535 ?????? E ?????? E1:E8 ???? ????? ?????? ?????? ??? ?? ???? ??? ???????? E10 ???? ???? ???? ???- ? ????? =VAR (E1:E8) ???? Enter ???? ?????? 311.8055 ?????? ????? =PV (present Value) ??????????? ? ???????? ??????? ???? ?????????? ?????? ???? ??? ??? ???? ? ?????? ??? ??? ???? ???????? ????? ???????? ???? ???? ??????? ?? ?????????? ????? ???? ?????? ??? ???? ????????? ?????? ???? ???? ?? ?????? ????? ??% ??? ????? ? ???? ??????? ????? ?? ???? ????? ????? ??? ??,??????? ??? ?? ??? ??? ????? ??? ??? ???? ???? ??? ???? ??????? ???? ??????? ??????? ??????? ?????? ??? ???? ?? ???? ?? ??????? ??????? ????? ?????? ??? ???????? C4 ???? ????? =PV(.12,10,10000) ???? Enter ???? C4 ??? ? 56502.23 ???? ????? ????? ???? ???? 50,000 ???? ????? ???????????? ?????? ?????? ???? ??????? =SLN (Cost,Salvage,Life) ??? ????? ???? ???? ?? Deperciation/???? ??? ???? ?????? ????? Cost= ??????? ????, Salvage= ?????? ??? ???? ??????? ????, Life= ?????? ???? ??? ???????? C5 ???? ????? =SLN (C1,C2,C3) ???? Enter ???? C5 ??? ? SLN Deperciation 14000 ??????? ?? ?????? ??????? =DB (Cost, Salvage, Life, Period, Month) ???????? ?????????
- 3. Cost = ???????? ???? ????? Salvage = ???????? ????? ???? ???????? ???? (????? ????)? Life = ????? ???? Period = ?? ????? ??? ???? ??? ?? ??? ???? Month = ???? ????? ??? ??? ?? ????? ??? ?????? ??? ??? ??? ???? ?? ????? ????? ?? ??? ??? ????? ? ?????? ??? ??? ???? ???????? ???? ???? ????? ???? ?????? ???????? ??? ?? ?? ???? ??? ?? ???????? ?? ???? ???? ? ???? ? ??? ?? ???????? ?????? ???? ?? ????? ???? ?? ?? ????? ????? ??? ?????? ???? ???? ?? Depreciation ???? ?????? ???? ????? ??? ??????? ???????? ??????? ??? ???? ????? ???? ???? ? ??? ???? ???? ???? ??-? = ? ?? ???? ????? ???? ???? ???? ? ??? ? = ?.? ? ???? ????? ????? ?????? ??????? ?? ??? ??? ????? ??? ???? ???? ???? ???? ?? ?? ????? ????? ? ??? ?? ???? ??? ? ?? ????? ????? ?? ????? ???? ? ?? ????? ?? ???? ??? ???? ?? ??? ??? ?? ???? ????? ??? ?? ????? ???? ?? ???? ?????? ??? ???? ???? ????? ???? Depreciation ?? ??? ?? ??? ??? ???? ????, ?????? ?? ??????? ?????? ??? ?? ????? ??? ???? =DB (C1,C2,C3,D2) ???? Enter ???? ??? ???????? E2 ???? ????? E2 ??? ? ????? ????? ???? ???? ????? E3 ?? ??? ???????? ??? =DB (C1,C2,C3,D3) ???? Enter ???? ????? Depreciation ??? ???? ????? E4, E5, E6 ???? ??, ?? ?? ?? ????? Depreciation ??? ??? ???? ???????? ????? ???? (Logical Function) =IF (Condition) ??????? ????? Condition ????? ?????? ?? ??? ??????? ??????? ?? ??? ?????? ?? ?? ????- = ???? ?? ??????? ???? > ??????? ? ??? < ??????? ? ????? >= ??????? ? ?? ?? ????? <= ??????? ? ???? ?? ????? <> ?????? ?????? ??????? ????????? AND, OR, NONE ?????? ???? ?????? ??? ??? ????? ????-
- 4. ??? ???? ???? ???? ??? ????? ???/ ????? ????? ???? ?? ?????? AND ????(1-1000) ??? ???? ???? ??? ????? ???/????? ?? ???? ???????? OR(1000 ????/ ??) ???? ????? ???? ??? ??? ???? ?? ??? ??? ??? ???? ???????? , (???) ???? ????? ???? ??? ??? ??????? ???? ????? IF ???? ?????? ??? ??? ????? ?? ???? ?????? ???? ???????? ???????? ???? ???? ??????? ??? ?????? ??? ???, ???? ?? ??? ??? ????? ?????? ?????-????????? ??????? ????? ???? ??????? ??? ???? ??? ?????????? ???? ???? ???? ? ?????? ??? ???? ?? ???? ????? ???? A+, ??? ?? ?? ???? ???? A, ??? ?? ?? ???? ???? A-, ??? ?? ?? ???? ???? B, ??? ?? ?? ???? ???? C, ??? ?? ?? ???? ???? D, ??? ?? ???? ???? Fail ?? F ??? ?????? =IF ?????? ?????? ??? ??????? ??? ???? ???? ???? ??? ???????? D2 ???? ????? =IF (C2>=800,A+, IF(C2>=700,A,IF(C2>=600,A-,IF(C2>=500,B, IF (C2>=400,C,IF(C2>=330,D,F)))))) ???? Enter ???? ???????? ???????? SSC ???????? ??? ????? ??????? ??? ?????? ?????? ???? ????? ????? ???? ?? ??? ?????-????????? ??????? ?????? ??????? ??????? ??? ???? ??? ???? ?????????? ???? ???? ???? ??????? ??????? ?????-???????? ?????? ??? ???? ????? ??? ??? ????? ????? 80 ?? ???? ????? ???? ??????? ??? A+ , 70-79 ????? ???? A, 60-69 ????? ???? A-, 50-59 ????? ???? B, 40-49 ????? ???? C, 33-39 ????? ???? D, ?? ?? ??????? ???? ???? ??? ?? F ???? ???? ??????? ????? 40 ?? ???? ??? ???? ??? ???????? ???? ??????? ???? ?? ???? ???? ???? ?????? ?????? ??? ????? ????? ??? ????? ?????????? ???? ???? ???? ?????? ???? ???? ??????? ???????? ??? ???? ???? ??????? ???????? N2 ?? ???? =IF (M2>40,M2-40,0) Enter. ??? ???????? O2 ?? ???? =AVERAGE (C2:L2)+N2/10 Enter. ??? ???????? P2 ?? ???? =IF(OR(C2<33,D2<33,E2<33,F2<33,G2<33,H2<33,I2<33,J2<33,K2<33,L2<33),Fail,Pass ) ??? ???????? Q2 ?? ???? = IF(AND(O2>= 80, P2=Pass),A+, IF(AND(O2>=70,P2=`Pass),A, IF(AND (O2>=60, P2=`Pass),A-, IF (AND(O2>=50,P2=`Pass),B, IF (AND (O2>=40, P2=`Pass),C, IF(AND (O2>=33,P2=`Pass),D,F)))))) Enter. ???????? ???????? ?????? ???????? ??????? ??? ??????
- 5. ??? ???, ???? ?????? ?????? BSC ????? ????? ????? ???????? ???? ???? ???? ??????? ?????, ??????, ???? ????????, ?????, ???? ??????? ????? ????? ????? ???? ??? ???? ???? ?? ??? ???? ????? ?? ?? ???? ????? ???? ???? ??? ???? ????? ???? ???? ??????? ??????? ???? ????? ??? ?? ?? ???? ???? ??? ???? ??? F ???? ???? ??? ???? ?? ???? ?????? ??? ?? ?? ??? D, ??? ???? ?? ???? ?????? ??? ?? ?? ???? C, ??? ???? ?? ???? ?????? ??? ?? ?? ???? B, ??? ???? ?? ???? ?????? ??? ?? ?? ???? A-, ??? ???? ?? ???? ?????? ??? ?? ?? ???? A, ??? ???? ?? ???? ???? A+ ???? ??? ?? ?????? ??????? ???????? ????????? ??????? ????? ?? ???????? ???? ???? ??? ???????? I2 ???? ????? =IF(OR(C2<33,D2<33,E2<33,F2<33,G2<33),`F,IF(AND(H2>0,H2<165),`F, IF(AND(H2>=165, H2<200),`D,IF(AND(H2>=200,H2<250),`C, IF(AND(H2>=250,H2<300),`B, IF(AND(H2>=300,H2<350),`A- IF(AND(H2>=350,H2<400),`A,`A+))))))) ??????? ???? Enter ???? Tax ??? ???? ????? ??? ???? ???? ???? ????? ?? ???? ?? ????? ?% ??????, ????? ?? ???? ??? ?% ?????? ??? ????? ????? ???????? ???? ???? ?????? ??? ???????? C2 ???? ????? =IF(AND(B2>5000, B2<10000), B2*.02,IF(B2>10000,B2*.05,NONE)) ???? Enter ???? ????????? ????? ??? ????? ????? ?????? ??? ??????? ???? ????? ???? ???? ????? ??? ???? ??? ??? ???? ?????????? ???????? ????? ???? ??? ?????? ???? ??? ?????? ?????? ????????? ?????? ?????? ???????????? ????? ???? ????? ??? ???? ???? ??, ???? ???????? ?????? ??? ??? ???? ??,??? ???? ?? ????? ???????? ????? ????? ???? ????, ???? ?????? ??? ??? ???? ?,??,??? ????? ?? ?? ????? ???????? ????? ?? ??? ???? ????? ???? ????? ???????? ??? ???????? ???? ???????? ???? ??? ??? =IF ????? ?????? ??? ?????? ???? ? ?????? ??? ???????? D2 ???? ????? =IF(OR (B2-C2<80000,B2<100000),B2*.10,B2*.11) Enter ???? ????????? ??????? ????? ????? ????? ??? ???? ??? ???, ??????? ?? ? ?? ???????? ??? ?? ???????? ????? ????? ?????? ????? ?????? ???? ???????? ? ????? ?? ??? ???? ?? ????? ??? ????? ????? ????? ?? ????? ?? ??? ???? ?????? ?? ????? ? ????? ??? ???? ?? ???? ????=??? ????? ???? ? ?????? ?? ?? ??? ? ????? ???
- 6. ???? ???? ???? = ??????? ???????? ? ?????? ???? ??? ???? ?????? ????? ?????? ??? ????? ???? ??????? ?? ??? ??? ?? ????? ??? ???? ????? ???? ????=???, ????=??, ?? ?????? ????? ??? ???+??=???????? ?????????? ???? Wage Sheet ???? ???? ???? ?????? ????????? ?? ?????? ???? ????? ???? ???? ? ???? ????? ??? ???? ? ?????? ???????? ???????? ???? ???? ??? ???????? D5 ???? ??? =IF(C5>8,C5-8,0) Enter ? ??? ???????? E5 ???? ??? =IF(D5>0,D5*20+8*15,C5*15) Enter ? D5:E10 ??????? ??? ??? ???????? D2 ?? ???? Shift ???? ??? E2 ?? ???? Fill Handel ? ??? ??? ????? ??? ????? ???? E10 ? ??? Enter ???? ???? C5 ???? 8 ???? ???? ????? C6 ???? 11 ???? ???? C7 ???? 7 ???? ???? C8 ???? 13 ???? ???? C9 ???? 9 ???? ???? C10 ???? 15 ???? ???? ??? ???????????? ???? ???? ?????? ????? ??? ?? ??? ????? ?? ??? ??? ?????? ?????? ????? ??? ??? ?????? ??? ??? ????? ??? ???????: ????? ??? ????? ???? ??????? ??? ????? ?????? ?????? ????? ???????? ???? ???? ?????? ??? ??? ? ???? ??? ????? ?? ???? ?.?? ????, ??? ???? ??? ????? ?? ???? ?.?? ????, ??? ???? ??? ????? ?? ???? ?.?? ???? ??? ??? ???? ??? ????? ????? ?.?? ???? ??? ??? ???? ?????? ??? ??? ????? ???? ? ????? ????? ???????? ??? ???????? ???????? ???? ??? =IF ????? ?????? ???? ?????? ?????? ? ?????? ???????? ???????? ???? ???? ??? ???????? D2 ???? ????? =IF(C2<=200,C2*1.75,IF(C2<=400,C2*2.50,IF(C2<=500,C2*3.75,C2*4.50))) Enter ? D2 ?? Fill Handel ????? ??? ?????? ????????????? ????? ???? ????? Salary Sheet ?????? ?????? ????? ?????? ?? ???? ?? ?? ????? ?? ???????? ???? ??? ????? ??? ?? ?????? ?????? ??? ???? ???? ??? ??? ???? ???? ??? ?? ???? ???? House Rent Basic ?? ??%, Medical Allowance, Basic ?? ??%, Provident Fund Basic ?? ??%, Income Tax Basic ???? ?? ???? ??? ?, ????-???? ?? ???? ?% ??? ???? ????? ??? ???% ? ? ?????? ???????? ???????? ???? ????D2 ????=C2*50%, E2 ????=C2*10%, F2 ????=C2*10% ???? ???? ???? G2 ???? ??? ?? ??? ??????? ??????? ???? ???? ???? =IF(C2<2000,0,IF(AND(C2>2000,C2<=5000),C2*5%,IF(C2>5000,C2*10%))) Enter ? H2 ???? =C2+D2+E2-(F2+G2) ???? ???? ????
- 7. D2:H2 ??????? ??? H10 ?? ???? Fill Handel ????? ??? ?????? ?? ????? ?? ???????? ????? ???? ????? Data ?? ? ???? ?? ???? ???? ???????: ???? ??? ?? Information ?? ?????? ?? ??? ?? ???????? ??????? ??? ??? ????? ????, ?????? ???????? ???????? ???? ?? ???????? ???? ??????? ??????? ??? ?????? ? ???? ????? ???? ????? ?? ??? ?? ?????????????? ???? ????? Data base ?? ? ?????? ????????? ????? ???????? ??? ?????? ?? ??????? ??? ??? ??? ?????? ?? ???? ?????? ?? ???? ?? ????? Flower base ?? ??? ?????? ???? ?? ??? ?????????? ???? ???? ?? ??????? ???? ?? ????? ?????? ???????? ??? ????? ?????????? ?? ???????? Data base ?? ???? ?????? ??? ??? Data Table ?????? ??? ???, ???? ????? ???? ?% ???? ????? ???? ?? ????? ??? ????? ????? ????? ? ???? ?% ???? ?????? ?? ??? ? ????? ??? ??????? ??? ??%, ??%, ??%, ??%, ??% ??? ?????? ?? ??? ? ????? ??? ??? ??? ??? (????? ???? ?? ?????????) ??????? ?? ????? ?????? ?? ??? ? ? ?? ?????? ??????????? ???? ??????? ?????? ???? ??? ??????? ??????-? ???????? ????? A3 ???? ?????? ??????? ??? Format>Column>Width. ?????? ???? 12 ???? Enter ???? A ?????? ????????? ???????? ??? ???? ???? ??? ???????? B4 ???? ???? =+B1*B2*B3+B1 ???? Enter ???? ???? ???? A4 ???? B9 ??????? ??? Data >Table ??? ???? ???? ??? ????? ???? ?????? ???? ????? ?????? ?????? Column input Cell ? ??? ??? B2 ???? ??? ???? ???? ???? ?????? ???? ???? ??? ????? ??? ???? ????????? ????? ??? ????? ? ?????? ??????C? ???????? ????? ??? ???????? A4 ???? ???? =+B1*B2*B3+B1 ???? Enter ???? ???? B4 ???? 10000, C4 ???? 50000, D4 ???? 80000 ???? ???? ???? A4 ???? D9 ??????? ??? Data >Table ??? ???? ???? ??? ????? ???? ?????? ???? ????? ?????? ?????? Row input Cell ? ??? ??? E1 ???? ??? ???? ???? ?????? ?????? Column input Cell ? ??? ??? B2 ???? ??? ???? ???? ???? ?????? ???? ???? ??? ????? ??? ???? ????????? ????? ??? ????? ????????-Goal Seek:
- 8. ??? ??? ???? ?????? ??? ????? ???? ?? ????? ???? ???? ????? ??? ???? ?? ???? ??? ???? ????? ??? ???? ?? ???? ????? ?????????? ???? ??????? ???? ???? ? ???????? ????? ??????? ??? ?? ??? ???? ?? ???? ??? ???? ?????? ??? ??????? ????? ???? ?? ????? ???? ?? ??? ???? ??????? ??? ????? ??? ??? ???? ???? ???? ???? ??? ?????? ?????? ???? ????? ????? ???? ?? ???? ?? ????? ???? ?? ????????? ?????? ???? ????? ??? ???? ????? ????? ????? ??? ?%, ?????? ?? ???, ????? ???????? ???? ????, ???? ?????? ? ??? ??? ???? ?????? ???????? ?,??,??? ????? ??????? ??? ???????? ???? ???? =PMT (B2/12,B3*12,B1) ???? Enter ???? ??? ????? ??????? ???? ????.?? ????? ?????? ???? ???? ???? ?,??? ????? ????? ?? ???? ?? ????? ???? ????? ?????? ???? ??? ???? ???? ????- ??? ???????? B5 ???? ????? ??????? ??? ???? ???? ???? ???? ???? ?????? ?????? ????? ???? ???????? ???? B1 ???? ??? ???? ???? ?????? ?????? ?????? ????? ???? ???????? ???? ?? ??????? ???? ???? ?? ????? ???? ????? ????????? ????? ????? ????? Data Auto Filter = ? ?????? ???? ????? ?? ???? ???? ???????? ????? ???? Data ?????? ??? ??? ???? ?????? Alt+D ????? ???? Filter ? ??? ??? Auto Filter ? ??? ???? ???? ?????? ?????? ????? ??? ????? ???? ???? ???? ?????? ? Formula ?????? ??? ?????? Logical Function ?? ??? ??? ??? ?? ????? Logical Function : ???? ??? ????? ??????? ????? ??? ???? ???? Logical Function ?? ??? ????? ???? ????? ??? ????? ??? ???, ???? ???? ???? ???? ?????? ????? ??? ??? ??? ???????? ??????? ??????? ??? ?? ???? ?????? ??? ??? ??????? ?????? ???? ???? ???? ???? # ?????? ???? Result Sheet ?? ???????? ???? ?? ???? ???? MS Excel 12-1 # ??? ???? ?? ??????? ????? ? ?? ??????? ??????? ???? ???? ?? ??? ???/??? ??? ???? ????? ???? ?????? ???? ?????? ?? ???? ???? # Result ?? Row ?? ????? ????? Shofik ?? ????? ??? ????? ???? ???? ????? ??? ????? ?? Logical Function ?? ?????? ???? ???? ????? ?????? ??? ?????????? G6 ??? ????? # ??? ???? ????? ??? ???? ??? ????? ?? ????? ???? ???? ????? G6 ???? ????? ??????? ???? Enter ????? ???? =IF(OR(B6<40,C6<40,D6<40), Fail, Pass)
- 9. # Enter ????? ???? ???? ???? ????? ??? ????? ??? ?? ??????? ??? ??? ?????? ??????? ????? ???? Result ?? ??? ????? ???? ????? ????? ???? ???? # Result ?? ??? Pass ???? Fail ??? ????? ?????? ????? ???? ???? ??? ??? Formula Bar ?? ???? ??????? ???? ????? # ??? ???? ?? ???????? ??????? ??????? ???? ????? ???? ???? ??? ??? ??????? ???????? ???? 80+=A+, 70+=A, 60+=B, 50+=C, 40+=D ??? 40-=F ?? ??? ???? ??? ??????? ???? ?????? ??????? ?? ????? ??? ??? ???? ???? ????? ??? ??? ?? ?????? ?? ???? ??? Result ??? ?? ??????? ??????? ????? ???? ???? ??? ??????? ??? ??? ????? ?? F ????? ????, ?????? ?? ??????? ??????? ????? ????? # H6 ???? ????? ??????? ???? Enter ????? ???? =IF(G6=Fail,F,IF(F6>=80,A+,IF(F6>=70,A,IF(F6>=60, B,IF(F6>=50,C,IF(F6>=40,D,F)))))) # Enter ????? ???? ???? ???? ??????? ??????? ??? ????? ??? ?? ??????? ??? ??? ?????? ??????? ????? ???? Grade ?? ??? ????? ???? ????? ??????? ??????? ???? ???? MS Excel 12-3 # Grade ?? ??? Result ??? ????? ?????? ???? ????? ???? ???? ??? ??? Formula Bar ?? ???? ??????? ???? ?????