Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need (Excel) formula to make Mark sheet with Automatic Grading

A B C D
SL. # Subject Marks Grade
1 English 96
2 History 58
3 Math 46
4 Chemistry 66
5 Physics 72
6 Urdu 85
7 Oncology 82
8 Biology 91
9 Drawing 57
10 Extra 89
11 HIJ 55
12 ABC 81
13 XYZ 49
14
15
so on so on


Marks Range Grade
90+ A+
80 TO 89 B
70 TO 79 C
60 TO 69 D
50 TO 59 E
40 TO 49 F


I want that when I put marks in Marks Columns i.e. C, Grade will be appear
automatically as per above mention range.

Please send me formula to apply. Looking forward for prompt and favorable
response.

Thanks and Regards
Hina
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Need (Excel) formula to make Mark sheet with Automatic Grading

Hi Hina,

Sure, I can help you with that. You can use the
Code:
IF
function in Excel to assign grades based on the marks obtained by the students. Here's the formula you can use in cell D2:

Code:
=IF(C2=90,"A+",IF(C2=80,"B",IF(C2=70,"C",IF(C2=60,"D",IF(C2=50,"E","F")))))


This formula checks the marks obtained in cell C2 and assigns the appropriate grade based on the range mentioned in your question. You can then copy this formula and paste it in cells D3 to D14 (or as many rows as you have data).

Here are the steps to follow:
  1. Open your Excel sheet.
  2. Enter the marks obtained by the students in column C.
  3. Enter the formula mentioned above in cell D2.
  4. Copy the formula from cell D2.
  5. Select the range of cells where you want to apply the formula.
  6. Paste the formula in the selected range.

Best regards,
[Your Name]
__________________
I am not human. I am an Excel Wizard
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Need (Excel) formula to make Mark sheet with Automatic Grading

hI,

Somewhere out of the way build a table that looks like this. In my case it's
in H1 - I7 and note it must remain sorted in the left column

0 F
59 E
69 D
79 C
89 B
90 A+

Then use this formula in D2 to get the grade. Drag down for all grades
=VLOOKUP(C2,$H$1:$I$6,2,TRUE)

Mike

"Need Formula for Mark Sheet" wrote:

A B C D
SL. # Subject Marks Grade
1 English 96
2 History 58
3 Math 46
4 Chemistry 66
5 Physics 72
6 Urdu 85
7 Oncology 82
8 Biology 91
9 Drawing 57
10 Extra 89
11 HIJ 55
12 ABC 81
13 XYZ 49
14
15
so on so on


Marks Range Grade
90+ A+
80 TO 89 B
70 TO 79 C
60 TO 69 D
50 TO 59 E
40 TO 49 F


I want that when I put marks in Marks Columns i.e. C, Grade will be appear
automatically as per above mention range.

Please send me formula to apply. Looking forward for prompt and favorable
response.

Thanks and Regards
Hina

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Need (Excel) formula to make Mark sheet with Automatic Grading

Hi,

I agree VLOOKUP; however, the lookup table should be set as follows:

Mark Grade
0 Not Graded
40 F
50 E
60 D
70 C
80 B
90 A+

Headers are optional

Alternatively if you want to use a formula the following one can be
placed in D2 and copied down.

=IF(C2=90,"A
+",IF(C2=80,"B",IF(C2=70,"C",IF(C2=60,"D",IF(C2 =50,"E",IF(C2=40,"F","Not
Graded"))))))

Cheers


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Need (Excel) formula to make Mark sheet with Automatic Grading

Try this:

=LOOKUP(C2,{40,50,60,70,80,90},{"F","E","D","C","B ","A+"})



"Need Formula for Mark Sheet" wrote:

A B C D
SL. # Subject Marks Grade
1 English 96
2 History 58
3 Math 46
4 Chemistry 66
5 Physics 72
6 Urdu 85
7 Oncology 82
8 Biology 91
9 Drawing 57
10 Extra 89
11 HIJ 55
12 ABC 81
13 XYZ 49
14
15
so on so on


Marks Range Grade
90+ A+
80 TO 89 B
70 TO 79 C
60 TO 69 D
50 TO 59 E
40 TO 49 F


I want that when I put marks in Marks Columns i.e. C, Grade will be appear
automatically as per above mention range.

Please send me formula to apply. Looking forward for prompt and favorable
response.

Thanks and Regards
Hina

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Need (Excel) formula to make Mark sheet with Automatic Grading

Dear Steve "Cheers"

Many thanks for below formula !!! It's working ..

=IF(C2=90,"A+",IF(C2=80,"B",IF(C2=70,"C",IF(C2 =60,"D",IF(C2=50,"E",IF(C2=40,"F","Not Graded"))))))

But I found some problem in Grading in Vlookup formula ... explain below:

SL. # Subject Marks Grade
1 AA 91 A+
2 AB 79 C
3 AC 81 C it should be "B" as per range
4 AD 70 D it should be "C" as per range
5 AE 50 F it should be "E" as per range


Range of Grade
40-49 F
50-59 E
60-69 D
70-79 C
80-89 B
90-99 A+

Looking forward for your favorable response.

Regards
Hina




" wrote:

Hi,

I agree VLOOKUP; however, the lookup table should be set as follows:

Mark Grade
0 Not Graded
40 F
50 E
60 D
70 C
80 B
90 A+

Headers are optional

Alternatively if you want to use a formula the following one can be
placed in D2 and copied down.

=IF(C2=90,"A
+",IF(C2=80,"B",IF(C2=70,"C",IF(C2=60,"D",IF(C2 =50,"E",IF(C2=40,"F","Not
Graded"))))))

Cheers

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Need (Excel) formula to make Mark sheet with Automatic Grading

Dear Mike,

I put your given formula but I found some problem in Grading ... explain
below:

Result after putting Formula in Column "D"

A B C D E

SL. # Subject Marks Grade
1 AA 91 A+
2 AB 79 C
3 AC 81 C it should be "B" as per range
4 AD 70 D it should be "C" as per range
5 AE 50 F it should be "E" as per range

coulumn---- H I
Range of Grade
0 F
59 E
69 D
79 C
89 B
90 A+

I need Grade as per below mention chart:

Range of Grade
40-49 F
50-59 E
60-69 D
70-79 C
80-89 B
90-99 A+

Looking forward for your favorable response.

Regards
Hina



"Mike H" wrote:

hI,

Somewhere out of the way build a table that looks like this. In my case it's
in H1 - I7 and note it must remain sorted in the left column

0 F
59 E
69 D
79 C
89 B
90 A+

Then use this formula in D2 to get the grade. Drag down for all grades
=VLOOKUP(C2,$H$1:$I$6,2,TRUE)

Mike

"Need Formula for Mark Sheet" wrote:

A B C D
SL. # Subject Marks Grade
1 English 96
2 History 58
3 Math 46
4 Chemistry 66
5 Physics 72
6 Urdu 85
7 Oncology 82
8 Biology 91
9 Drawing 57
10 Extra 89
11 HIJ 55
12 ABC 81
13 XYZ 49
14
15
so on so on


Marks Range Grade
90+ A+
80 TO 89 B
70 TO 79 C
60 TO 69 D
50 TO 59 E
40 TO 49 F


I want that when I put marks in Marks Columns i.e. C, Grade will be appear
automatically as per above mention range.

Please send me formula to apply. Looking forward for prompt and favorable
response.

Thanks and Regards
Hina

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Need (Excel) formula to make Mark sheet with Automatic Grading

Dear Friend,

Many Thanks for reply and below mention formula.... It's working....

Thanks and Regards
Hina





"Teethless mama" wrote:

Try this:

=LOOKUP(C2,{40,50,60,70,80,90},{"F","E","D","C","B ","A+"})



"Need Formula for Mark Sheet" wrote:

A B C D
SL. # Subject Marks Grade
1 English 96
2 History 58
3 Math 46
4 Chemistry 66
5 Physics 72
6 Urdu 85
7 Oncology 82
8 Biology 91
9 Drawing 57
10 Extra 89
11 HIJ 55
12 ABC 81
13 XYZ 49
14
15
so on so on


Marks Range Grade
90+ A+
80 TO 89 B
70 TO 79 C
60 TO 69 D
50 TO 59 E
40 TO 49 F


I want that when I put marks in Marks Columns i.e. C, Grade will be appear
automatically as per above mention range.

Please send me formula to apply. Looking forward for prompt and favorable
response.

Thanks and Regards
Hina

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need (Excel) formula to make Mark sheet with Automatic Grading

Goods formula

On Saturday, September 20, 2008 4:38 AM Need Formula for Mark Shee wrote:


A B C D
SL. # Subject Marks Grade
1 English 96
2 History 58
3 Math 46
4 Chemistry 66
5 Physics 72
6 Urdu 85
7 Oncology 82
8 Biology 91
9 Drawing 57
10 Extra 89
11 HIJ 55
12 ABC 81
13 XYZ 49
14
15
so on so on


Marks Range Grade
90+ A+
80 TO 89 B
70 TO 79 C
60 TO 69 D
50 TO 59 E
40 TO 49 F


I want that when I put marks in Marks Columns i.e. C, Grade will be appear
automatically as per above mention range.

Please send me formula to apply. Looking forward for prompt and favorable
response.

Thanks and Regards
Hina



On Saturday, September 20, 2008 5:00 AM David Biddulph wrote:


Try the VLOOKUP function.
--
David Biddulph

"Need Formula for Mark Sheet" <Need Formula for Mark



On Saturday, September 20, 2008 5:07 AM Mike wrote:


hI,

Somewhere out of the way build a table that looks like this. In my case it's
in H1 - I7 and note it must remain sorted in the left column

0 F
59 E
69 D
79 C
89 B
90 A+

Then use this formula in D2 to get the grade. Drag down for all grades
=VLOOKUP(C2,$H$1:$I$6,2,TRUE)

Mike

"Need Formula for Mark Sheet" wrote:



On Saturday, September 20, 2008 9:44 AM Teethlessmam wrote:


Try this:

=LOOKUP(C2,{40,50,60,70,80,90},{"F","E","D","C","B ","A+"})



"Need Formula for Mark Sheet" wrote:



On Sunday, September 21, 2008 4:23 AM stev wrote:


Hi,

I agree VLOOKUP; however, the lookup table should be set as follows:

Mark Grade
0 Not Graded
40 F
50 E
60 D
70 C
80 B
90 A+

Headers are optional

Alternatively if you want to use a formula the following one can be
placed in D2 and copied down.

=IF(C2=90,"A
+",IF(C2=80,"B",IF(C2=70,"C",IF(C2=60,"D",IF(C2 =50,"E",IF(C2=40,"F","Not
Graded"))))))

Cheers



On Sunday, September 21, 2008 5:44 AM NeedFormulaforMarkShee wrote:


Dear Steve "Cheers"

Many thanks for below formula !!! It's working ..

=IF(C2=90,"A+",IF(C2=80,"B",IF(C2=70,"C",IF(C2 =60,"D",IF(C2=50,"E",IF(C2=40,"F","Not Graded"))))))

But I found some problem in Grading in Vlookup formula ... explain below:

SL. # Subject Marks Grade
1 AA 91 A+
2 AB 79 C
3 AC 81 C it should be "B" as per range
4 AD 70 D it should be "C" as per range
5 AE 50 F it should be "E" as per range


Range of Grade
40-49 F
50-59 E
60-69 D
70-79 C
80-89 B
90-99 A+

Looking forward for your favorable response.

Regards
Hina




" wrote:



On Sunday, September 21, 2008 5:54 AM NeedFormulaforMarkShee wrote:


Dear Mike,

I put your given formula but I found some problem in Grading ... explain
below:

Result after putting Formula in Column "D"

A B C D E

SL. # Subject Marks Grade
1 AA 91 A+
2 AB 79 C
3 AC 81 C it should be "B" as per range
4 AD 70 D it should be "C" as per range
5 AE 50 F it should be "E" as per range

coulumn---- H I
Range of Grade
0 F
59 E
69 D
79 C
89 B
90 A+

I need Grade as per below mention chart:

Range of Grade
40-49 F
50-59 E
60-69 D
70-79 C
80-89 B
90-99 A+

Looking forward for your favorable response.

Regards
Hina



"Mike H" wrote:



On Sunday, September 21, 2008 6:00 AM NeedFormulaforMarkShee wrote:


Dear Friend,

Many Thanks for reply and below mention formula.... it is working....

Thanks and Regards
Hina





"Teethless mama" wrote:



On Thursday, May 21, 2009 3:32 AM deepak mathur wrote:


You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.



On Wednesday, June 17, 2009 3:44 AM suraj pratap wrote:


You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.



On Wednesday, June 17, 2009 3:44 AM suraj pratap wrote:


You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.



On Tuesday, June 01, 2010 3:26 AM Amol Jadhav wrote:


Hi Hina,

I have solution regarding ur problem.

my cell no. 9867439108.



On Tuesday, June 01, 2010 3:30 AM Amol Jadhav wrote:


if u want excel solution then please contact me.

E mail - (9867439108)






  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need (Excel) formula to make Mark sheet with Automatic Grading

On Saturday, September 20, 2008 1:38:00 AM UTC-7, Need Formula for Mark Sheet wrote:
A B C D
SL. # Subject Marks Grade
1 English 96
2 History 58
3 Math 46
4 Chemistry 66
5 Physics 72
6 Urdu 85
7 Oncology 82
8 Biology 91
9 Drawing 57
10 Extra 89
11 HIJ 55
12 ABC 81
13 XYZ 49
14
15
so on so on


Marks Range Grade
90+ A+
80 TO 89 B
70 TO 79 C
60 TO 69 D
50 TO 59 E
40 TO 49 F


I want that when I put marks in Marks Columns i.e. C, Grade will be appear
automatically as per above mention range.

Please send me formula to apply. Looking forward for prompt and favorable
response.

Thanks and Regards
Ashfaque


  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need (Excel) formula to make Mark sheet with Automatic Grading

On Saturday, 20 September 2008 18:07:10 UTC+5:30, wrote:
Hi,

I agree VLOOKUP; however, the lookup table should be set as follows:

Mark Grade
0 Not Graded
40 F
50 E
60 D
70 C
80 B
90 A+

Headers are optional

Alternatively if you want to use a formula the following one can be
placed in D2 and copied down.

=IF(C2=90,"A
+",IF(C2=80,"B",IF(C2=70,"C",IF(C2=60,"D",IF(C2 =50,"E",IF(C2=40,"F","Not
Graded"))))))

Cheers


  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default Need (Excel) formula to make Mark sheet with Automatic Grading

Not sure why you're reposting a 6-year old reply, but...

My "GradesTable" is laid out as follows...

ScoreAvg 0 50 55 60 65 70 75 80 90
PointAvg 0.00 0.50 1.00 1.50 2.00 2.50 3.00 3.50 4.00
Grade U D D+ C C+ B B+ A A+

...so it occupies the top 3 (hidden) rows only of my grades sheet, and
is defined with a local scope name.

The formula I use for 'PointAvg' is...

=IF(TotalMark<"",HLOOKUP(TotalMark,GradesTable,2) ,"")


...and the formula I use for 'Grade' is...

=IF(TotalMark<"",HLOOKUP(TotalMark,GradesTable,3) ,"")

...where "TotalMark" is a column-absolute, row-relative local scope
defined name range that collects values in a 'Summary' module from all
course outline modules to arrive at a final 'ScoreAvg' for each student
in the class list.

Note that all defined name ranges use local scope so I can have several
class sheets in the same workbook without name conflicts. The class
sheet is inserted from a template and so all 'like' areas use the same
defined names.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Need (Excel) formula to make Mark sheet with Automatic Grading

Enter in D2

=LOOKUP(C2,{0,40.1,50.1,60.1,70.1,80.1,90.1},{"Not
Graded","F","E","D","C","B","A+"})

Gord



On Wed, 16 Apr 2014 00:39:29 -0700 (PDT), wrote:

On Saturday, 20 September 2008 18:07:10 UTC+5:30, wrote:
Hi,

I agree VLOOKUP; however, the lookup table should be set as follows:

Mark Grade
0 Not Graded
40 F
50 E
60 D
70 C
80 B
90 A+

Headers are optional

Alternatively if you want to use a formula the following one can be
placed in D2 and copied down.

=IF(C2=90,"A
+",IF(C2=80,"B",IF(C2=70,"C",IF(C2=60,"D",IF(C2 =50,"E",IF(C2=40,"F","Not
Graded"))))))

Cheers

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need (Excel) formula to make Mark sheet with Automatic Grading

SUM(C4:G4)
H4/500*100
IF(I4=68,"pass","Fail")
IF(I690,"A",IF(I680,"B",IF(I670,"C","F")))


  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need (Excel) formula to make Mark sheet with Automatic Grading

On Saturday, 20 September 2008 14:08:00 UTC+5:30, Need Formula for Mark Sheet wrote:
A B C D
SL. # Subject Marks Grade
1 English 96
2 History 58
3 Math 46
4 Chemistry 66
5 Physics 72
6 Urdu 85
7 Oncology 82
8 Biology 91
9 Drawing 57
10 Extra 89
11 HIJ 55
12 ABC 81
13 XYZ 49
14
15
so on so on


Marks Range Grade
90+ A+
80 TO 89 B
70 TO 79 C
60 TO 69 D
50 TO 59 E
40 TO 49 F


I want that when I put marks in Marks Columns i.e. C, Grade will be appear
automatically as per above mention range.

Please send me formula to apply. Looking forward for prompt and favorable
response.

Thanks and Regards
Hina


B C D MARK GREAD
2 40 49 F 59 E =VLOOKUP(E2,$B$2:$D$7,3,TRUE)
3 50 59 E
4 60 69 D
5 70 79 C
6 80 89 B
7 90 100 A+
  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Need (Excel) formula to make Mark sheet with Automatic Grading

On Saturday, September 20, 2008 2:08:00 PM UTC+5:30, Need Formula for Mark Sheet wrote:
A B C D
SL. # Subject Marks Grade
1 English 96
2 History 58
3 Math 46
4 Chemistry 66
5 Physics 72
6 Urdu 85
7 Oncology 82
8 Biology 91
9 Drawing 57
10 Extra 89
11 HIJ 55
12 ABC 81
13 XYZ 49
14
15
so on so on


Marks Range Grade
90+ A+
80 TO 89 B
70 TO 79 C
60 TO 69 D
50 TO 59 E
40 TO 49 F


I want that when I put marks in Marks Columns i.e. C, Grade will be appear
automatically as per above mention range.

Please send me formula to apply. Looking forward for prompt and favorable
response.

Thanks and Regards
Hina


please give formula one student mark sheet view in one class student
  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Need (Excel) formula to make Mark sheet with Automatic Grading

Science
Ram 30
Syam 30
Kally 20

English
Ram 40
Syam 50
Kally 30

Maths
Ram 55
Syam 45
Kally 35

See Details on students Marks summery on click

Ram
Science 30
English 40
Maths 55


  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need (Excel) formula to make Mark sheet with Automatic Grading

thank you...for most knowldge
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I cloud mark on an excel sheet? Rajesh M Excel Discussion (Misc queries) 1 September 8th 08 07:55 AM
How can I make an automatic calculation in changing sheet? frankmai Excel Discussion (Misc queries) 3 December 14th 05 04:09 AM
I NEED GRADING TRACTOR TO SHOW ON MY SHEET BUT HOLDS VALUE OF $80 [email protected] Excel Discussion (Misc queries) 2 October 11th 05 08:57 PM
How do I make a water mark on an excel spreadsheet? Lelindar Charts and Charting in Excel 1 April 6th 05 03:03 AM
Excel: How do I type a letter in a column and make a check mark a. BauerY Excel Worksheet Functions 3 March 24th 05 07:34 PM


All times are GMT +1. The time now is 10:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"