sets

Excel
=IF(SIGN(C7-B7)=1,”Profit”,IF(SIGN(C7-B7)=-1,”Loss”,”None”))

=IF(MOD(A2,2)=0,”Even”,”Odd”)

% Change in 2 years:
=ABS((C4-B4)/B4*100)
नयाँ बर्ष – पुरानो वर्ष / पुरानो वर्ष

Set 37
=IF(AND(C3=”Married”,D3>15000),D32%,IF(AND(C3=”Single”,D3>15000),D35%,D3*1%))

set 36
(Operator Exam Marks Calculation)
=C2*1-(D2*20%)-E2*0

Set 35
Running or Retired? in English date:
=IF(DATEDIF(B10,TODAY(),”y”)<58,”Running”,”Retired”)

In Nepali date:= IF(DATEDIF(H7,”2078-09-18″,”y”)<58,”Running”,”Retired”)
Date Format (YYYY-MM-DD) मा गएर Location मा Nepal छानियो ।

text wihout space=SUBSTITUTE(A1,” “,””)
Total character with space=LEN(A1)
Total character without space=LEN(SUBSTITUTE(A1,” “,””))
Total space=LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))

CDCB => ,”a”,)

Consonants निकाल्दा ६ वटा substitute मा स्पेशको लागि १ वटा
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A8),”a”,),”e”,),”i”,),”o”,),”u”,),” “,)
Vowels निकाल्दा २२ वटा substitute मा स्पेशको लागि १ वटा
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A8),”b”,),”c”,),”d”,),”f”,),”g”,),”h”,),”j”,),”k”,),”l”,),”m”,),”n”,),”p”,),”q”,),”r”,),”s”,),”t”,),”v”,),”w”,),”x”,),”y”,),”z”,),” “,)

EMI निकाल्ने VVI=PMT(ब्याजदर/12, वर्ष*12, मुल्य-डाउन पेमेन्ट, 0)
=PMT(B5/12,B4*12,B2-B3,0)
ब्याजदर प्रतिशतमा हुनैपर्छ ।

Gannt Chart बनाउँदा
Bar Chart >> Stacked Bar लिने ।
Category : Task1, Task 2…. राख्ने ।

• Reverse Date
• No fill
• Minumum/Maximum Date Value

Array Formula
=MAX(IF((A2:A11=”shampoo”)*(B2:B11=”Babita”),C2:C11))

– IF पछि दुइटा ब्राकेट
– Range को विचमा =
– दुइटा ब्राकेटले क्लोज गर्ने
– लास्टमा Ctrl+Shift+Enter गर्ने ।

Array Formula – Set 30
Custom Format मा गएर 0″ बोरा”=SUM(C2:C7*D2:D7)
यो फर्मुला हानेपछि Ctrl+Shift+Enter हान्ने । हाने पछि={SUM(C2:C7*D2:D7)} फर्मुला यस्तो बन्छ आफै ।
यसलाई अर्को तरिकाबाट पनि निकाल्न सकिन्छ ।
=SUMPRODUCT(C2:C7,D2:D7) – यो Array फर्मुला होइन ।

Set 29
Entry Time, Out Time
Hour Worked निकाल्दा
=(D3-C3)*24 तर अन्त्यमा General Format बनाउनै पर्‍यो ।
अर्को तरिका पनि छ तर त्यसमा दशमलव आउँदैन ।
त्यो हो
=HOUR(D3-C3) 
तर अन्त्यमा General Format बनाउनै पर्‍यो ।

Wages निकाल्दा
=IF(E2<=8,E2*150,1200+(E2-8)*300)=IF(E2<=8,E2*(1200/8),1200+(E2-8)*(2400/8))

Set 28
Electricity Bill > Domestic, Industries, Agriculture
=IF(C2=”Domestic”,IF(F2<=20,80,IF(F2<=250,80+(F2-20)*7.3,80+230*7.3+(F2-250)*11.25)),IF(C2=”Industries”,IF(F2<=20,200,IF(F2<=250,200+(F2-20)*10.15,200+230*10.15+(F2-250)*15.55)),IF(C2=”Agriculture”,IF(F2<=20,50,IF(F2<=250,50+(F2-20)*5.4,50+230*5.4+(F2-250)*7)))))

Set 27

Result Only हो भने
=IF(MIN(C2:G2)<32,”Fail”,”pass”)
Result with Absent हो भने
=IF(COUNTIF(C2:G2,”abs”),”Absent”,IF(MIN(C2:G2)>32,”Pass”,”Fail”))
Result with Absent and Supplimentary हो भने
=IF(COUNTIF(C2:G2,”abs”),”Absent”,IF(MIN(C2:G2)>32,”Pass”,IF(COUNTIF(C2:G2,”<32″)<=2,”Supplimentary”,”Fail”)))
यहाँ बुझ्नु पर्ने कुरा के हो भने Count if को Criteria राख्दा ” ” भित्र राख्नुपर्‍यो ।

Set 26

Count the Number of Failed Subjects
=IF(C3<32,1,0)+IF(OR(D3<24,E3<8),1,0)+IF(G3<32,1,0)+IF(OR(H3<24,I3<8),1,0)

Set 24

Calculate no of year
=DATEDIF(B2,C2,”y”)
Calculate number of Month
=DATEDIF(B2,C2,”m”)
Calculate number of days
=DATEDIF(B2,C2,”d”)
Calculate Total (years, months and days)
=DATEDIF(B7,C7,”y”)
=DATEDIF(B7,C7,”ym”)
=DATEDIF(B7,C7,”md”)
Calculate Total years, month and days in words
=DATEDIF(B7,C7,”y”)&” Years “&DATEDIF(B7,C7,”ym”)&” months “&DATEDIF(B7,C7,”md”)&” days”

Electricity Bill

=IF(E4<=20,80,IF(E4<250,80+(E4-20)*7.7,80+(230*7.7)+(E4-250)*9.9))

Set 22

Interest Rate
=IF(B3<50000,5,IF(B3<100000,6,IF(B3=100000,7,IF(B3<800000,8,IF(B3=800000,9,IF(B3<1000000,10,15)))))+IF(C3>24,1,0))
< भएपछि > हुनुहुँदैन । र अन्त्यमा कमा (,) नदिएर + दिएर कन्डिसन लगाउने ।
+IF(C3>24,1,0))अगाडी रेटको कन्डिसन सकेपछि टोटल ब्राकेटमा एउटा घटाएर अर्को + को कन्डिसनमा जाने ।

Set 18

Game remarks
=IF(RANK(H2,$H$2:$H$9)<=5,”Next Round”,”Eliminated”)Rank को आधारमा टप ५ लाई Next Round अरुलाई eliminate
Rank निकाल्दा निविर्षिनु पर्ने कुरा चाँही पछि लेख्ने Ref/Range लाई Absolute Reference/Fixed बनाउनैपर्छ ।

SET 10

=IF(C3*12<200000,0,IF(C3*12<=300000,C3*15%,C3*20%))
यदि वार्षिक तलब २ लाख र ३ लाखको बीचमा हुँदा ३ लाख वा बराबर हान्नुपर्‍यो ।
C3*12<=300000

Mega Set
=IF(AND(C2<15000,D2<>”kathmandu”),C2*10%,0)
if 10% whose salary is less than 15000 and not from Kathmandu.

set 8

=IF(MAX(B2:E2)>90,”A+”,IF(F2>60,”A”,IF(F2>50,”B”,IF(F2<50,”C”))))
यहाँ झुक्किने भनेको IF(MAX(B2:E2)>90,”A+”
A+ if any subject’s marks is more than 90, otherwise according to percentage…

Word

३ वटा पेज मध्ये पहिलो पेजमा बाहेक अरु बाँकी पेजमा पेज नंबर लगाउँदा
– Diffirent First Page
– Start From 0- Section Break
– Remove Link to Previous
– Start from 1
– Cover Page हालेपछि पनि Start from 0