Formula

greater than (>)   –-   से अधिक (>)

less than (<)   –-       से कम (<)

0%       =IF(Q48<250000,N51*0%,0)

5%    =IF(Q48<250000,Q48*0%,0)+IF(AND(Q48>250001,Q48<500000),(Q48-250000)*5%,IF(Q48>500000,250000*5%,0)) 

10%   =IF(Q48<500000,Q48*0%,0)+IF(AND(Q48>500000,Q48<750001),(Q48-500000)*10%,IF(Q48>750000,250000*10%,0))

15%

=IF(Q48<750000,Q48*0%,0)+IF(AND(Q48>750001,Q48<1000000),(Q48-750000)*15%,IF(Q48>1000000,250000*15%,0))

20%

=IF(Q48<100000,Q48*0%,0)+IF(AND(Q48>1000000,Q48<1250000),(Q48-1000000)*20%,IF(Q48>1250000,250000*20%,0))

25% 

=IF(Q48<1250000,Q48*0%,0)+IF(AND(Q48>1250001,Q48<1500000),(Q48-1250000)*25%,IF(Q48>1500000,250000*25%,0))

30%

= IF(Q48<1500001,0,((Q48-1500000)*0.3))

1.           izfr’kr fudkyus dk lq= 250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] 500000 ls 1000000 rd 20 izfr’kr] 1000000 ls Åij dh jkf’k ij 30 izfr’kr fudkyus dk lq=

1.    250000 ij ’kwU; izfr’kr    =IF(N47<250000,N47*0%,0)

2.    250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] =IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))

3.    500000 ls 1000000 rd 20 izfr’kr] =IF(AND(N47>500000,N47<1000000),(N47-500000)*20%,IF(N47>1000000,500000*20%,0)) 

4.    1000000 ls Åij dh jkf’k ij 30 izfr’kr

=IF(N47>1000000,(N47-1000000)*30%,0)

2.           250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr]

=IF(N47<250000,N47*0%,0)+IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))

3.           250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] 500000 ls 1000000 rd 20 izfr’kr]

=IF(N47<250000,N47*0%,0)+IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))+IF(AND(N47>500000,N47<1000000),(N47-500000)*20%,IF(N47>1000000,500000*20%,0))

4.           izfr’kr fudkyus dk lq= 250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] 500000 ls 1000000 rd 20 izfr’kr] 1000000 ls Åij dh jkf’k ij 30 izfr’kr fudkyus dk lq=

=IF(N47<250000,N47*0%,0)+IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))+IF(AND(N47>500000,N47<1000000),(N47-500000)*20%,IF(N47>1000000,500000*20%,0))+IF(N47>1000000,(N47-1000000)*30%,0)

/kkjk 87 A ds rgr NqV 2500

=IF(N47>500000,0,IF(N47<500000,2500))

Yes and No     =IF(X53="YES",2500,0)

1.           /kkjk 87 , ds rgr 2500 dh NqV 500000 ¼ 5 yk[k rd½ dk lq= %&

=IF(D12>500000,0,IF(D12<500000,2500))   ENTER   = 2500

= IF(N47>500000,0,IF(N47<500000,2500))

2.           lse  la[;k ykus ds fy, lq= =H10   Enter press

lse la[;k ykus dk lq=  =IF(L$19=0,0,L19)

lse la[;k ykus dk lq==IF(A$2=0,0,A2)

lse  l[;k  ykus ds fy,  ;g lq= vizsy efguk es fy[ks

=IF(J$12=0,0,J12)     =IF(J$12=0,0,J16)

3.       Surrender  Leave   =IF(L3="NO",0,IF(X3=3,D23,VLOOKUP(X3,B12:D23,3,FALSE)))/2

4.       HRA   dk lq=  =IF($AC$4=20%,ROUND(0.2*D13,0),ROUND(0.1*D13,0))

HRA    =IF($AC$4=20%,ROUND(0.2*D12,0),ROUND(0.1*D12,0))

HRA      =IF($AC$4=20%,ROUND(0.2*D19,0),ROUND(0.1*D19,0))

5.  HDA   dk lq=  %&=ROUND(D7+E7)/ 12,0)             ENTER

=ROUND(D7+E7)/100*12,0)    ENTER

=ROUND(D7+E7)*0.1,0)            ENTER

6.  HRA   dk lq=  =IF(AA2="NO",0,IF($AA$4=20%,ROUND(0.2*E9,0),ROUND(0.1*E9,0)))

7.  lys.Mj yho  =IF(AA7="NO",0,IF(AA11=2,E20,VLOOKUP(AA11,C9:E20,3,FALSE)))/2

Lys.Mj yho dk Mh-,- dk lq=            IF(AND(X3>3,X3<10),ROUND(136%*D26,0),ROUND(139%*D26,0))

=IF(AA14="YES",6774,0)

8.  jkT; chek dh dVkSfr dk fu;ekuwlkj =IF(D12<8501,400,IF(D12<11001,550,IF(D12<18001,1100,IF(D12<28001,1550,2650))))

9.           ;fn 'khV dk uke ns j[kk gSa rks  lq= uke ns ldrs gSaa  ='Other Deduction'!B10

10.      Åij ds lSy dh lse la[;k ykus dk lq=] =Q13   

uhps ds lSy esa ,Mªsl fy[kuk gSaA   ckjh ckjh ls  =Q12     =Q13    =Q14

11.  /kkjk 87, ds rgr]  2500 dh NqV ysus o ugh ysus dk lq= (YES/No) esaA=IF(X53="YES",2500,0)

12.  100% dk lq=  &  Åij okys lSy ds cjkcj jkf’k yjus ds fy,  =ROUND(N49/100*100,0)

13.  DA  Arrear  Gpf    =(ROUND(136%*D12,0)-ROUND(132%*D12,0))*3

14.  Fixation Arrear    =IF(I$12=0,0,ROUND(0.1*D28,0))

15.  Salary Arrear       =IF(I$12=0,0,ROUND(0.1*D29,0))

16.  RPMF   =IF($Z$2="No",IF(D19<7001,205,IF(D19<13001,341,IF(D19<21001,511,680))),0)

HRA  =IF($AC$4=16%,ROUND(0.2*D13,0),ROUND(0.1*D13,0))

NwV ?kkjk 87¼A½¼dqy dj ;ksXk vk; 3-50 yk[k rd ;k mlls de gks rks  vk; ij vk;dj dh NwV vf/kdre :- 2500 rd½

=IF(Q45>350000,0,IF(Q52<2501,Q52,2500))

=IF(N49>350000,0,IF(N55<2501,N55,2500))

=IF(N47>350000,0,IF(N53<2501,N53,2500))

17.                     tqykbZ  ekg esa lq= yxkuk gSa  100 :Ik;s ij 3 izfr'kr

   =ROUNDUP(ROUND(1.03*D15,0),-1)

vFkkZr  =ROUNDUP(ROUND(1.03*D15,0),-1)    xq.kk vkbZdu ds ckn tqu ekg dk lSy ,Mªsl fy[kuk gSa  =ROUNDUP(ROUND(1.03*D15,0),-1)

18.                     Lis'ky is spl. Allonce   25 % - ACB  ds fy, dk lq= &

=ROUND(Baisec pay cell address/100*25,0)   Enter key nck;s

=ROUND(D12/100*25,0)   Enter key nck;s

19.                     1   DA  =ROUND(132%*E9,0)

=ROUND(136%*E10,0)

=ROUND(139%*E16,0)

=ROUND(9%*E16,0)

20.                     2   DA   =ROUND(E10/100*132,0)

=ROUND(E10/100*136,0)

=ROUND(E10/100*139,0)

=ROUND(E10/100*9,0)

21.                     HDA  12 %ds fy, =ROUND((C10+D10)/12,0)      Enter

22.                     C.P.F 10 %ds fy, =ROUND((D13+H13)/100*10,0)       

23.                     10% va'knku jkf'k tek ds fy, lq=

=ROUND((D13+H13)/100*10,0)        Enter nck;

24.                       vk;dj ij 5 izfr'kr VSDLk fudkyus dk lq=

=ROUND((N47-250000)/100*5,0)   Enter key nck;sA       

=ROUND(N48/100*5,0)

25. nksuks la[;k es ls NksVh la[;k Kkr djus ds fy, lq=  

lq=  =MIN(L29,N20)

lq=  =MIN(L29,150000)

lq=  =MIN(2500,0)

26.  nksuks cell address  ds chp dh la[;k es ls NksVh la[;k Kkr ¼ekyqe½ djus ds fy, lq=  

=MIN(C6:G99) 

27.  nksuks la[;k es ls cM+h la[;k Kkr djus ds fy, lq=
=Max(L29,N20)

=Max(C6:G99) 

28.  dqy dj ;ksx vk; ¼nl ds xq.kuQy esa½   /kkjk  288A

29.  tSls la[;k 816 ds vfUre nks vad dks nl ds xq.kuQy esa fy[kus ds fy,

=ROUND(A1,-1)Enter press

tSls 816  dh txg 820 la[;k gks tk,xh  

¼vfUre la[;k 5 ;k 5 ls vf/kd gSa rks 15 dks 20 es fy[kk tk,xk½

¼vfUre la[;k 5 ls de gSa rks 14 dks 10 es fy[kk tk,xk½

30.  tSls la[;k 25816 ds vfUre rhu vad dks lkS ds xq.kuQy esa fy[kus ds fy,

=ROUND(A1,-2)Enter press

tSls 25816 dh txg 25900 la[;k gks tk,xh  

¼vfUre la[;k 50 ;k 50 ls vf/kd gSa rks 150 dks 200 es fy[kk tk,xk½

¼vfUre la[;k 50 ls de gSa rks 140 dks 100 es fy[kk tk,xk½

31.  tSls la[;k 25816 ds vfUre rhu vad dks gtkj ds xq.kuQy esa fy[kus ds fy,

=ROUND(A1,-3)Enter press

tSls 25816 dh txg 26000 la[;k gks tk,xh  

¼vfUre la[;k 500 ;k 500 ls vf/kd gSa rks 1550 dks 1600 es fy[kk tk,xk½

¼vfUre la[;k 1550 ls de gSa rks 1540 dks 1500 es fy[kk tk,xk½

32.  cksul ds fy, lq=&=IF(Z4="Yes",6774,0)

33.  DA  ds fy, lq= &=ROUND(132%*D12,0)   Enter press

=ROUND(136%*D13,0)  Enter press

=ROUND(139%*D19,0)   Enter press

34.  100 ij 3 izfr'kr osru o`f) tqykbZ ekg esa &

lq= =ROUNDUP(ROUND(1.03*D15,0),-1)

lq= =ROUNDUP(ROUND(1.03*D15,0),-2)

lq= =ROUNDUP(ROUND(1.03*D15,0),-3)

lq= =PRODUCT(1.03,D15)  Enter key dks nck;s

=PRODUCT(1.03,D15(tqu ekg ds lsy ,Mªsl fy[kuk gS) )  Enter key dks nck;s