- Yeh hindi excel tutorial 100% FREE hai <\/li>
- Iss course mein humne aapko simple examples<\/strong> se excel ke baare mein bataya hai<\/li><\/ul>\n\n\n\n
\n\n\n\nYeh tutorial Hindi MS Excel full course in Hindi ka 4th part hai. Agar aap chahte hai to aap pichle bhaag bhi kar sakte hai. <\/strong><\/p>\n\n\n\n
Niche diye gaye links par click karke aap uss tutorial pe ja sakte hai – <\/p>\n\n\n\n
MS Excel in Hindi full tutorial <\/a>Beginner course part 1<\/a><\/strong> – Agar excel bilkul nahi aati<\/a><\/p>\n\n\n\n
MS<\/a> Excel in Hindi full tutorial <\/a>Beginner course part 2<\/a><\/strong> – MS Excel ko use karke data analysis kaise karte hai (Sort aur Filter kaise kare)<\/a><\/p>\n\n\n\n
MS Excel in Hindi full tutorial <\/a>Advance course part <\/a><\/strong>1 – MS Excel formulas and Functions simple wale<\/a> <\/p>\n\n\n\n
\n\n\n\nChaliye dekhetein ke intermediate mein aap konse formulas sikhenge.<\/p>\n\n\n\n
Intermediate level formulas aur functions<\/strong><\/h2>\n\n\n\n
Intermediate level mein hum niche diye gaye formulas aur functions apply karna sikhenge \u2013<\/p>\n\n\n\n
Logical functions:<\/strong><\/strong><\/p>\n\n\n\n
IF- <\/strong>Tests the validity of a logical expression and returns the result \u201cTRUE\u201d if true and \u201cFALSE\u201d if false<\/p>\n\n\n\n
AND- <\/strong>Returns TRUE if all of the arguments evaluate to TRUE.<\/strong><\/p>\n\n\n\n
OR- <\/strong>Returns TRUE if any argument evaluates to TRUE.<\/strong><\/p>\n\n\n\n
Chaliye ab dekhte hain inn functions ko ek example se.<\/p>\n\n\n\n
Main yahan class record ki report ka ek simple example liya hai.<\/p>\n\n\n\n
Ye mera data hai-<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-Marks-record-2.jpg\")
<\/figure><\/div>\n\n\n\n3.<\/strong> LOGICAL FUNCTIONS:<\/strong><\/h3>\n\n\n\n
Aur isme humara aim hai<\/strong> \u2013\n\n<\/p>\n\n\n
\n\n
\n \n a. PASS in English<\/p>\n<\/td>\n
\n IF function<\/p>\n<\/td>\n<\/tr>\n
\n \n b. PASS in English and Maths<\/p>\n<\/td>\n
\n AND function<\/p>\n<\/td>\n<\/tr>\n
\n \n c. PASS in PCME<\/p>\n<\/td>\n
\n Nested IF with AND statements<\/p>\n<\/td>\n<\/tr>\n
\n \n d. PASS in Optional \/ Additional<\/p>\n<\/td>\n
\n OR function<\/p>\n<\/td>\n<\/tr>\n
\n \n e.\u00a0 Grading<\/p>\n<\/td>\n
\n IF(multiple)<\/p>\n<\/td>\n<\/tr>\n
\n \n f. PASS\/FAIL in All<\/p>\n<\/td>\n
\n Nested IF with AND, OR statements<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n
NOTE:<\/strong> Koi bhi function lagaane se pehle aap apni cell range dhyan se check karle aur accordingly lagayein kyonki meri aur aapki cell range different ho sakti hain.<\/p>\n\n\n\n
a. PASS in English<\/strong><\/p>\n\n\n\n
IF function<\/strong><\/p>\n\n\n\n
If function ka use hum logical test karne ke liye karte hai aur ismein agar humari inserted logical condition true hai toh humein true<\/strong> ki value milti hai aur agar false hota hai toh false<\/strong> ki value milti hai. <\/p>\n\n\n\n
Yahan par agar humein pata lagana hai ki koi English mein pass hai ya nahi toh hum IF function ka use kar sakte hain. Yahan par humne kisi ke English mein PASS hone ki condition marks in English >= 34 <\/strong>li hai.<\/strong><\/p>\n\n\n\n
Chaliye ab dekhein ki ye function kaise lagatein hai-<\/p>\n\n\n\n
Sabse pehle cell mein likhe-<\/p>\n\n\n\n
=IF(<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-Eng-IF-func-1.jpg\")
<\/figure><\/div>\n\n\n\nUske baad IF<\/strong> function mein aap apna logical test<\/strong> ya aapki condition for passing in English enter kare aise-<\/p>\n\n\n\n
=IF(D11>=34 <\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-Eng-IF-func-2-1.jpg\")
<\/figure><\/div>\n\n\n\nIske baad ek comma<\/strong> lagakar\naap apne logical test<\/strong> ki value <\/strong>jo aap chahtein ki aapko mile jab test\ntrue<\/strong> hai inverted commas mein enter kare aise-<\/p>\n\n\n\n
=IF(D11>=34,\u201dPASS\u201d<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-Eng-IF-func-4.jpg\")
<\/figure><\/div>\n\n\n\nAb comma <\/strong>lagakar vovaluelikhein jo aap chahtein hain aapko mile jab logical test <\/strong>ki value\nfalse <\/strong>hai aise-<\/p>\n\n\n\n
=IF(D11>=34,\u201dPASS\u201d,\u201dFAIL\u201d)<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-Eng-IF-func-5.jpg\")
<\/figure><\/div>\n\n\n\n
Press ENTER<\/strong>.<\/p>\n\n\n\n![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-Eng-IF-func-6.jpg\")
<\/figure><\/div>\n\n\n\nIss cell ko agar aap niche drag karenge toh ye IF logical test baaki cells ke liye bhi apply hojayega aise-<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-Eng-IF-func-7.jpg\")
<\/figure><\/div>\n\n\n\n![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-Eng-IF-func-8.jpg\")
<\/figure><\/div>\n\n\n\nb. PASS in English and Maths<\/strong><\/p>\n\n\n\n
AND function<\/strong><\/p>\n\n\n\n
AND function ka use hum ek se jyada arguments ko ek sath test karne ke liye karte hain.<\/p>\n\n\n\n
Iss function mein jo bhi tests hum insert karte hain agar vo sab\ntrue hain toh humein ye function lagane par aapko apne cell me \u201cTRUE\u201d<\/strong>\nmilega aur agar inn tests mein se ek bhi false hai to humein cell mein\n\u201cFALSE\u201d milega<\/strong>.<\/p>\n\n\n\n
Ab imagine kare ki humein iss example mein pata lagana hai ki\nkonse students English aur Hindi dono<\/strong> mein pass hain<\/strong>. Iske liye\nhum AND function ka use kar sakte hain. <\/strong><\/p>\n\n\n\n
Chaliye ab dekhein ki ye function kaise lagta hai-<\/p>\n\n\n\n
Sabse pehle cell mein likhe-<\/p>\n\n\n\n
=AND(<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-eng-and-maths-AND-func-1.jpg\")
<\/figure><\/div>\n\n\n\nAb ismein apna pehla logical test likhein. Yahan mera pehla logical test ki kya student English mein pass hai, uske liye humari condition hai marks in<\/strong> English > =34<\/strong>. Iss test ko likhne ke liye aap vo cell select kare jismein aapke English ke marks hain ya phir vo cell no. type kare jismein aapke English ke marks hain. Uske baad >=34<\/strong> likhe dein aise-<\/p>\n\n\n\n
=AND(D11>=34<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-eng-and-maths-AND-func-2.jpg\")
<\/figure><\/div>\n\n\n\nIske baad comma lagayein aur apna second logical test likhein. Yahan humara second logical test hai marks in maths >= 34<\/strong>. Isse aise likhein-<\/p>\n\n\n\n
=AND(D11>=34,E11>=34<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-eng-and-maths-AND-func-3.jpg\")
<\/figure><\/div>\n\n\n\nAb bracket close kare. Aapka pura function kuch aisa hoga-<\/p>\n\n\n\n
=AND(D11>=34,E11>=34)<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-eng-and-maths-AND-func-3.2.jpg\")
<\/figure><\/div>\n\n\n\nPress\nENTER.<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-eng-and-maths-AND-func-4.jpg\")
<\/figure><\/div>\n\n\n\nAapko\nyahan \u201cTRUE<\/strong>\u201c ya \u201cFALSE<\/strong>\u201d milega. Agar \u201cTRUE\u201d hai toh iska matlab\naapke AND function ke dono logical test true hain aur agar \u201cFALSE\u201d toh matlab\nkam se kam ek condition false hai.<\/p>\n\n\n\n
NOTE<\/strong>: Humne yahan sirf do logical test\/conditions enter ki\nhain but aap comma lagakar apne according aur bhi insert kar sakte hain aise-<\/p>\n\n\n\n
=AND(D11>=34,E11>=34,F11>=34) <\/strong><\/p>\n\n\n\n
Ab\niske according TRUE ya FALSE milega aapko.<\/p>\n\n\n\n
Ab\naap isse niche drag karke baaki students ke liye bhi iss function ko apply kar\nsakte hain.<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-eng-and-maths-AND-func-5.jpg\")
<\/figure><\/div>\n\n\n\nc. PASS in PCME <\/strong><\/p>\n\n\n\n
Nested IF with AND function<\/strong><\/p>\n\n\n\n
Nested IF function hum tab use\nkarte hain jab humein ek se jyada conditions ya logical tests IF function mein\nadd karne hote hain.<\/p>\n\n\n\n
Yahan humein jo PCME-Physics,\nChemistry, Maths, English<\/strong> inn sab mein pass<\/strong> hain unka pata karna hai\nisliye hum yahan IF function mein AND function lagayenge taaki hum ek se jyada\nlogical tests ke liye TRUE\/FALSE laga sake.<\/p>\n\n\n\n
Isse hum aise lagayenge-<\/p>\n\n\n\n
=IF(AND(<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-pcme-IF-and-AND-nested-func1.jpg\")
<\/figure><\/div>\n\n\n\n![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-pcme-IF-and-AND-nested-func2.jpg\")
<\/figure><\/div>\n\n\n\nAb\nismein apna pehla logical test insert kare jo hai English marks>=34 aise-<\/p>\n\n\n\n
=IF(AND(D11>=34<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-pcme-IF-and-AND-nested-func3.jpg\")
<\/figure><\/div>\n\n\n\nAb\nismein comma lagakar apne baaki logical tests insert kartein rahe aur fir AND\nstatement ke liye bracket close karde aise-<\/p>\n\n\n\n
=IF(AND(D11>=34,E11>=34,\nF11>=34, G11>=34)<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-pcme-IF-and-AND-nested-func4.jpg\")
<\/figure><\/div>\n\n\n\nAb\nismein comma<\/strong> lagakar aap vo value jo apke logical tests ke according true\nvalue<\/strong> ke liye ho vo inverted commas (here \u201cPASS\u201d) <\/strong> mein likhdein aise-<\/p>\n\n\n\n
=IF(AND(D11>=34,E11>=34, F11>=34, G11>=34),\u201dPASS\u201d<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-pcme-IF-and-AND-nested-func5.jpg\")
<\/figure><\/div>\n\n\n\nAb\ncomma lagakar jo value logical tests ke liye false mein ho vo inverted\ncommas (\u201cFAIL\u201d) <\/strong>mein likhdein aur IF function ke liye bracket close karde\naise-<\/p>\n\n\n\n
=IF(AND(D11>=34,E11>=34,\nF11>=34, G11>=34),\u201dPASS\u201d,\u201dFAIL\u201d)<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-pcme-IF-and-AND-nested-func6.jpg\")
<\/figure><\/div>\n\n\n\nPress\nENTER.<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-pcme-IF-and-AND-nested-func7.jpg\")
<\/figure><\/div>\n\n\n\nIss\ncell ko niche drag karke baaki cells mein yahi function corresponding cells\nmein apply karle –<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-pcme-IF-and-AND-nested-func8.jpg\")
<\/figure><\/div>\n\n\n\nd. PASS in Optional \/ Additional<\/strong><\/p>\n\n\n\n
OR function<\/strong><\/p>\n\n\n\n
OR function ka use hum ek se jyada arguments ko ek sath test karne ke liye karte hain. <\/p>\n\n\n\n
Iss function mein jo bhi tests hum\ninsert karte hain agar vo unme se ek bhi true hain toh humein ye function\nlagane par apne cell me \u201cTRUE\u201d<\/strong> milega aur agar inn tests mein sabhi\nfalse hai to humein cell mein \u201cFALSE\u201d milega<\/strong>.<\/p>\n\n\n\n
Ab imagine kare ki humein iss\nexample mein pata lagana hai ki konse students optional ya additional\nsubject mein se kisi bhi ek mein pass hain<\/strong>. Iske liye hum OR function ka\nuse kar sakte hain. <\/strong><\/p>\n\n\n\n
Chaliye ab dekhein ki ye function\nkaise lagta hai-<\/p>\n\n\n\n
Sabse pehle cell mein likhe-<\/p>\n\n\n\n
=OR(<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-optional-OR-func1.jpg\")
<\/figure><\/div>\n\n\n\nAb\nismein apna pehla logical test likhein. Yahan mera pehla logical test ki kya\nstudent optional subject mein pass hai ya nahi, uske liye humari condition hai marks\nin<\/strong> optional >=34<\/strong>. Iss test ko aap aise likhe-<\/p>\n\n\n\n
=OR(H11>=34<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-optional-OR-func2.jpg\")
<\/figure><\/div>\n\n\n\nIske\nbaad comma lagayein aur apna second logical test likhein. Yahan humara second\nlogical test hai marks in additional >= 34<\/strong> aur phir OR function ke\nliye apne bracket close kare. Isse aise likhein-<\/p>\n\n\n\n
=OR(D11>=34,E11>=34)<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-optional-OR-func3.jpg\")
<\/figure><\/div>\n\n\n\nPress\nENTER.<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-optional-OR-func4.jpg\")
<\/figure><\/div>\n\n\n\nIss\ncell ko niche drag kare aur baaki cells mein apply kare- <\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-optional-OR-func5.jpg\")
<\/figure><\/div>\n\n\n\ne. Grading<\/strong><\/p>\n\n\n\n
IF(multiple)<\/strong><\/p>\n\n\n\n
IF function mein kabhi kabhi\nhumein true ya false ke liye bhi IF function lagana hota hain. Jaise agar kisi\nstudent ke marks >=85 hai toh uska grade hai A, agar marks >= 75 hai but\n< 85 hai toh uska grade B hoga. Aisi conditions jismein aapke logical tests\nke bhi true\/false ke logical tests ho hum vahan IF multiples times use karte\nhain. <\/p>\n\n\n\n
Ise yahan hum grade karne ke liye\nuse karegenge. Sabse pehle likhe-<\/p>\n\n\n\n
=IF(<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-IF-multiple-1.jpg\")
<\/figure><\/div>\n\n\n\nAb ismein apna pehla logical test\ninsert kare aur phir comma<\/strong> lagakar inverted commas (here \u201cA\u201d)<\/strong> mein apne logical tests ki true ke liye value\nlikhe aise-<\/p>\n\n\n\n
=IF(D11>=85,\u201dA\u201d<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-IF-multiple-2.jpg\")
<\/figure><\/div>\n\n\n\nAb kyonki aapko false ki value\nmein ek logical test likhna hai isliye ab aap ek comma<\/strong> lagakar dusra\nIF function<\/strong> lagaye aise-<\/p>\n\n\n\n
=IF(D11>=85,\u201dA\u201d,IF(<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-IF-multiple-3.jpg\")
<\/figure><\/div>\n\n\n\n
\n<\/strong>Ab ismein apne pehle IF function ki false value ke liye jo\nlogical test<\/strong> lagana hai vo lagaye aur phir comma<\/strong> lagakar uski true\nvalue inverted commas (here \u201cB\u201d) <\/strong>lagaye aise-<\/p>\n\n\n\n=IF(D11>=85,\u201dA\u201d,IF(D11>=75,\u201dB\u201d<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-IF-multiple-4.jpg\")
<\/figure><\/div>\n\n\n\nAb aise hi aap comma lagakar second IF function<\/strong> ke liye jo bhi false value<\/strong> mein aap third IF function<\/strong> se logical test lagana chahte hai vo lagadein aise-<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-IF-multiple-5.jpg\")
<\/figure><\/div>\n\n\n\nAb ismein apna logical test enter\nkare-<\/p>\n\n\n\n
=IF(D11>=85,”A”,\nIF(D11>=75,”B”, IF(D11>=65,\u201dC\u201d<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.joshtalks.com\/joshkosh\/wp-content\/uploads\/2019\/07\/Example-IF-multiple-6-1.jpg\")
<\/figure><\/div>\n\n\n\nAb saare required logical tests\ninsert karne ke baad aap sabhi IF statements ko close karne ke liye multiple\nbrackets lagade aise-<\/p>\n\n\n\n