# Thread: MS Excel Main Result Tayar Karain  Step By Step

1. ## MS Excel Main Result Tayar Karain  Step By Step

Doostoon, WSLM::

Mairay Pahlay wali file ( http://itdunya.com/showthread.php?t=82565 ) buhat advanced level key thi aur mujhay pata hay ka beginners / intermediate level kay liye bhi buhat mushkal thi  Iss Liye main Formulas key parts kar diye hay ta kah aap logoon ko samjh aa saky .. InshaAllah Akhar main hum tamam formulas ko Ek sath bhi likhna seekhani gay .. Ek cheez ka khayal karain kah Jump laganay key koshish bilkuk bhi nah karain keonkah jub tuk aap kay concept clear naheen hongay aap Excel main advance level key koi cheez bhi naheen bana saktay. Rutta idhar bilkul bhi naheen chalay ga sirf aur sird Conceptual work .

Neechay kuch detail hay file kay baray main  aap xls key file open kar kay uss main changes kar saktay hain jaisay main waqtan Fowaqtan aap logoon ko skikhta rahoon ga . Allah Kay Naam Say Shorou Kartay hain

In attached image I have 6 column from A to G.
Column A contains Students Names
Column B to F contains Subjects and below their marks
Column G have Obtained Marks
Column H have Percentage
Column I : Status Fail / Pass
Column J : Grade
Column K : If Fail then Subject Name (yah hum sub say akhar main seekhan gay

Lesson-1  Calculation 1 .. (Calc 1 = Column G = Obtained Marks)
Hum Ali kay tamam subjects ko Add Jama karma Chahtay hain) Hum .
1st Tareeka
App likhain G2 cell main Ja kar =B2+C2+D2+E2+F2
App aisay he total kartay hain na ???? Chalian Ge hamara Ans 299 Aagia .. Iss Key bajay ek function use kartay hain SUM()

2nd Tareeqa
SUM()
Syntax Banawat:
Sum(number1,number 2 . ) OR Sum(Range)

Aap Sum main , kay sath cell ka address bhi day saktay hain , Misal Key Toor par  aap nay G2 main likha SUM(B2,C2,D2,E2,F2) Ans. 299 Aagia ..
Aab 3rd tareeqay say kartay hain ..
SUM (RANGE)
Ghabranay key zaroorat naheen hay RANGE kia hay ??????
RANGE
Aap kissi bhi sheet main ja kar cell A1 say lay kar A8 tuk 1,2  8 likh dain aab Cell A11 main ja kar = sign dal kar A1 aur phir key board par say : colon dal dain aur phi A10 likh dain . Aap dakihain gay kah A1 say A10 tuk Blue Area ban gia hay  Bus yah range hay  Ghabrana naheen yah sirf range ka introduction tha ka : sign kia karata Hum nay issay kaha hay A1 say lay A10 tuak ka area select karay .. aab : sign iss main bata raha hay kah range nay kahan tauk ja na hay aap A10 key bajay C10 likhain aur daikhain kia ho ta hay .
Yahi batt hum Sum ko kah rahay hain kah Bhaijan Sum B2 say lay F2 tuk ka part ka sum karay .. =SUM(B2:F2) aap daikhain gay B2 say lay kar F2 tuk blue line draw ho gai hay . Aur Enter karnay par Hamaray Pass Total Marks Aa Gaiy ..
Aap ko 3 method pata lag gaiy hain Total Karnay kay ..

Mujhay pata hay ka Excel ka istaamal jananay walay kah rahain hoon gay kah Bachoon wali batain hain leekan jub tuk New Users ko Concept clear naheen ho ga meri sheet key samjh naheen aai gi . Fill hall itna he kafi hain  Next Lesson main jald he mulaqaat ho gi . Apni aara kay zaroor di jaiy ga ......

Allah Hafiz

2. ## Lesson- 2  Calculation 2 .. (Calc 2 = Column G = Percentage)

Lesson- 2  Calculation 2 .. (Calc 2 = Column G = Percentage)

Doostoon,
WSLM::

Aap main say aksar Percentage % nikalnay main phans jatay hoon gay aaj main buhat aasani kay sath issay maloom karnay ka method batata hoon

Example : Faraz karain aap nay 510 number liye hain 850 main aap percentage kaisay nikalain gain ?????? Percentrage = (Hasal Karda Number / (divided by) Total Marks) x 100
=(510/850)*100 = (.60)*100 = 60 . Yah to aap nay 8th Class main bhi parha hoga kah qoss Parenthesis ( ) kiss kaam aati hain ????  Iss ka matlab hay kah pahlay ( ) kay under wali value key calculation karay aur baad main doosir . Agar aap =510/(850*100) likh kar daikhay kia jawab aay ga ???? check karain
To janab aab aatain hay Excel sheet key taraf .

Formula in H2
=(G2/\$B\$15)*100

G2=Obtained Marks
B15=Total marks ( jo kah hum nay all subjects ko sum kar kay haasil kia hay)

\$ Absolute
Aap paraishan hoon gay kah \$ sign B15 kay aagay aur peechay keon daal dia hay . Yah Kissi bhi cell ko Constant mean Mustaqil bannay kay kaam aata hay yaani Aap agar formula ko dossray cell main drag karain gay to yah result aai ga

H3=(H3/\$B\$15)*100
H4=(H4/\$B\$15)*100
H5=(H5/\$B\$15)*100

Daikha Obtained marks tabdeel ho rahay hain keon kah Hum nay H cell ko absolute naheen bananya hay aur Total Marks to Constant honay chahiye keonkah Obtained Marks to change ho rahay hain leekn Total Marks to 500 he rahain gay .

To janab aab aa ko percentage nikalna bhi aa gia hay ..
Jalad he 3rd Lesson key taraf barhain gay .. once again keep in your mind that you must clear your concepts first then go forward in case of any query dont hesitate to contact with me

Fee Aman Allah

3. ## Lesson- 3  Calculation 3 .. (Calc 3 = Column I = Fail / Pass Status)

Lesson- 3  Calculation 3 .. (Calc 3 = Column I = Fail / Pass Status)

Yah thors sa confusing hoga leekan Aap tamam logon nay IF condition to parhi hi ho gi leekan shayed AND operator ka pata nah ho ?? Koi baat naheen Iss Lecture kay ikhtamam par clear ho jay ga .. dobara main kah raha hoon kah Rutta naheen lagana hay keonkah akhar main main tamam formulas ko ek he cell main likhoon ga ..

Aap I2 cell main likhain IF(B2>59,PASS,FAIL)
I2= IF(B2>59,PASS,FAIL)
Kia jawab aaya Fail
Aab aap B2 main value 60 kar adain
Kia jawab aaya Pass
Syntax (Banawat) of IF(Logical_Test,Value if True,Value if False)
Yaani hamaray formula hay

=IF(B2>59,PASS,FAIL)
Logical Test = B2>59 (Agar B2 bara ho 59 say to True warna False  Yaad rahay logical test sirf do value daita hay True / False (aap issay 1/0 bhi kah saktay hain keonkah excel true to hamiahsa 1 aur False ko 0 consider karta hay  yah cheez aap hay zahan main achi tarah aa gai to aap baray baray formulas bhi bana saktay hai)
If True = Pass Agar Logical Test True howa matlab B2 cell 59 say bara ho gia to Pass likha howa aa jay ga
If False=Fail Agar logical Test False howa matlab B2 cell 59 say bara nah howa to Fail.

Importanat thing : Pass aur True to hamisha   kay darmiyan likhain gay keonkah yah text hay aur if condition main true aur false main agar text likhni ho to inverted commas   main aay gi .... value ko aap sirf 1 ya 2 bhi likh saktay hain . Tajrab kar kay daikhain . IF(B2>59,1,0) . Kia jawab aaya ????
To janab aap mazeed tajrabat karain ta kah aap ka concept mazeed clear ho

Bhai Yah to howa aik cell par test agar woh C2 main fail ho gia to kia hoga ??? aap pahlay cell key bunyad par ussay kiss tarah pass kar saktay hain ???????? Ghabranay key zaroorat naheen hay hamaray pass ek operator hay AND() jissay hum IF kay sath istamaal karain gai ..

Syntax (banawat) of AND()
AND(logical1, logical2 .. upto 30)

Aap likhan I2 Cell main
=AND(B2>59,C2>59) ..Ans is False keonkah B2 main hum nay 59 likha howa hay aur AND ka matlab hay kah tamam conditions True ho gi to jawab True aay ga warna False he aay ga ..
Aap B2 main 60 aur C2 main bhi 60 likhain . Kia jawab aaya ??? True . Becoz donoon conditions True ho gain hain . Mazeed tajrabat kartay rahin agar koi sawal zahan main aay to lazmi poochiye ga ..
Aab aap tamam Cell key ooper check lagain gay tamam cell conditions ko pora kartay hain kah naheen .. Leekan pahlay B2 say lay kar F2 tuk tamam cells ko 59 say bara kar dain aur yah formula I2 main likhain ..
= AND(B2>59,C2>59,D2>59,E2>59,F2>59)
Aaap daikhain gay kah agar tamam cell B2 say lary F2 tuk 59 say baray hoon gay to True aay ga warna False

Aab aap doobara aain IF condition key taraf hum AND ko IF kay sath istamaal karain gay . Leekan pahlay B2 say lay kar F2 tuk tamam cells ko 59 say bara kar dain aur yah formula I2 main likhain
I2 =IF(AND(B2>59,C2>59,D2>59,E2>59,F2>59),"PASS","FAI L")

G janab aap ka guess bilkuk true hay pass likha howa aay ga keonkah Tamam condition True theen aur AND nay True return kia, iss waqat AND ek condition kay toor par kaam kar raha hay tou If main logical test True howa to pahlay konsa hissa aay ga ???? Zahir hay True wala jo kah pass hay
Aap B2 ko 50 karadain , kia jawab aaya ??????? G bilkul IF key condition False hoi and 2nd part main mojood False part aai ga jo kah fail hay

Umeed hay aap ka concept kissi had tuk clear howa ho ga  agar naheen to experiment kartay rahain aur aap kamyaab ho jain ga .. next lesson tuk ijaazat

Allah hafiz,

4. very niceeeeeeeeeeeeee

5. thanx veeeeeeeer niceeee keep it uppppppppp

6. Thanks Faheem & Hafiz Naveed for your encouragement ..

7. ## Lesson- 4  Calculation 4 .. (Calc 4 = Column J = Grade Use Nested IFs )

Lesson- 4  Calculation 4 .. (Calc 4 = Column J = Grade Use Nested IFs )

Aab hamara yah task hay kah agar student pass hay to iss column main uss ka Grade aa na chahiye aur Grade depend karta hay percentage par jo kah hum Lesson 2 main Aur Column H main rakhtay hain
Aou janab ghabranay key zaroorat naheen hay sirf aur sirf logic aur conept key baat hay  Aut aap achi tarah IF condition kay baray main jantay hain ..

1st Step.

IF(I2<>PASS,FAIL, --------

I2<>PASS yah lesson 3 main hum solve kar chukay hain kah pass aur Fail kiss tarah aata hay .
To janab Logical test bana IF I2 is not equal to Pass then Fail yaani agar I2 naheen hay PASS to Fail likh day .. Pata Laga ??? Janab Fail wala part iss surat main kaam karay ga jub I2 main PASS nah likh ho to agar Fail howa to zahir hay True and Pahal Hisa if condition ka kaam karay ga .. aur agar False howa to Next part ..

2nd Step
Dossri row main tamam logoon ko 60 say oper marks day dain yaani I2 cell main Pass likha howa aajay . Aab aatay hain formula key next part key tarf .

=IF(I2<>"PASS","FAIL",IF(H2>=90,"A",IF(H2>=80,"B", IF(H2>=70,"C",IF(H2>=60,"D")))))

Logical Test False how yaani ghalat kah I2 main Pass likha howa aa gia aab next part yaani Value if False wala part kaam karay ga .. aab aain hamay H2 cell key taraf jo hum nay lesson 2 main haasil kia tha yaani Percentage  to janab Grade key shart neechay di gai hay
A 90~100
B 80~89
C 70~79
D 60~69

Aab False part main 2nd IF condition lagai IF(H2>=90,A Yaani agar H2 main 90% marks hain to J2 main A likh day next condition IF(H2>=90,"A",IF(H2>=80,"B", A Agar 90 naheen hay to False hogai aur False part key taraf aai ga jiss main hum nay 3rd IF condition F(H2>=80,"B",lagai hoi hay agar yah bhi ghalat ho to 4th IF condition . Iss tarh Akhri condition bani kah agar IF(H2>=60,"D" agar H2 60 yah 60 say bara ho to Grade D likh day .
Aap ko pata lag gia hoga kah hum again & again IF condition ko use kar kay check laga saktay hain leekan khayal rahay kay hum sirf 7 nested IFs use kar saktay hain yaani 6 baar hum false part main if Likh likh kar aagay barhatay rahain gay agar aik aur dafa hum false part main IF likhain gay to Error aa jaiy gi iss say bachnay kay aur bhi buhat say tarkeeqay hain leekan woh phir sahi  Aap iss key practice karain aur student kay number aagay peechay kar kay Grade change hotay daikhain . Main doobara kahoon ga kah aap baar baar key practice aur experiment say hi concept clear kar saktay hain warna aap sirf copy write banay gain ..

I know most of the people just search the web for entertaining but few of them for knowledge, this lesson if for those persons who searched net for their knowledge but I know few of them will be able to find my articles and Ill be happy if they asked more question from me to clear their minds

Allah Hafiz,

8. Itnaa aap loog achi tarah seekh laitay hain to aap key next assignment hogi kah Akhri column "K" main Agar Student Fail ho to uss subject ka naam likha aa jay aur Pass ho to bus Pass likha howa aa jay .....

9. very nice.....
but yaar aap ye kaam urdu main karty tu aap ki post ko char chand lag jaty...

10. Actually i am out of touch from inpage from many months and even don't ve enough time to do this ... but if any one else is welcome to write the thread in Urdu .......
Leekan yaar main cheez hay interest! Aab main ya koi itnee mahnat kar kay inpage main likhay aur post karay magar jawab main koi response nah aai to kia faida keonkah 1 ya 2 din main thread doosray teesaray page par chala jay ga .....
Telling you honestly, main nay buhat koshish key kah koi pakistani technical forum ms office say related ho leekan mujhay buhat afsoos say kahna parh raha hay kuch forums milay leekan unn par response nah honay kay barabar hay .......
Main Nay UK & USA forum subscribe karwa rakhay hain aur udhar loggon key excel problem solve kar ta hoon aur appni queries bhi send karta hoon .. those forums are very informative but i wanted to do something for my home fellows but couldn't find any spark here for learning purpose ........

11. Member
Last Online
14th October 2009 @ 01:12 PM
Join Date
03 Oct 2008
Location
City In Heaven
Posts
4,964
160
Thanked
17
Waoooooooooo Sire......... Thanks Alot Buddy.. wel yaar agr aap ais ko urdu main likhty tu kamal he hojta.............
phir bhe bohat achi sharing hai aap ke
thnx alot..........

12. Senior Member+
Last Online
30th May 2020 @ 11:21 AM
Join Date
04 Jan 2009
Posts
225
20
Credits
1,062
Thanked
5
grade bananay k liyay if se acha hai ka lookup use karein to itna mushkil nahi lagayay ga.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•