Newbee


Joined
Jan 13, 2019
Messages
8
Reaction score
4
Hiya I am well over 60 and I am trying to teach myself IF statements in excel eg Students in an exam marks are out of 143

Betty got 123 out of 143 and what write and IF statement that will calculate the percentage and then award them either a Distinction equal of over 80%, Merit if equal or over 60% and a Pass if equal or over 40% and if below 40% a fail. Can anyone help me with this? Thank you in anticipation X
 

nivrip

Yorkshire Cruncher
Joined
Mar 21, 2007
Messages
8,496
Reaction score
1,471
Hi RedMoggy and welcome to PCR. :wave:

Not my field but there may be someone along shortly who can help. :)
 
Ad

Advertisements

Joined
Mar 14, 2018
Messages
533
Reaction score
170
This web site is a great place to start: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ifthenelse-directive

They talk about the structure of the "IF THEN" statement:
#If expression Then
statements
[ #ElseIf expression-n Then
[ elseifstatements ]]
[ #Else
[ elsestatements ]]
#End If

So basically you want the expression to evaluate to true to do the statements within its scope.
Dim testScore As Integer = 0
Dim award As String = ""
IF testScore >= 80 THEN
award = "Distinction"
ELSEIF testScore >= 60 THEN
award = "Merit"
ELSEIF testScore >=40 THEN
award = "Pass"
ELSE
award = "Fail"
END IF

I'm sure you can figure out the rest.
 
Joined
Jan 13, 2019
Messages
8
Reaction score
4
This web site is a great place to start: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ifthenelse-directive

They talk about the structure of the "IF THEN" statement:
#If expression Then
statements
[ #ElseIf expression-n Then
[ elseifstatements ]]
[ #Else
[ elsestatements ]]
#End If

So basically you want the expression to evaluate to true to do the statements within its scope.
Dim testScore As Integer = 0
Dim award As String = ""
IF testScore >= 80 THEN
award = "Distinction"
ELSEIF testScore >= 60 THEN
award = "Merit"
ELSEIF testScore >=40 THEN
award = "Pass"
ELSE
award = "Fail"
END IF

I'm sure you can figure out the rest.

Thank you very much for your response - unfortunately it doesnt mean very much to me at all. At the moment I have only got as far as IF, AND, OR statements/formulas and some simple nested IF statements. I was hoping if would be something that I could relate to or recognize and give me a written example so I could substitute my figures to get it to work. That's why I put my age as older you get the less ability you have to assimilate new ideas ect hence struggling with probably simple IF statements to someone with more ability than I (which actually wouldn't be hard).
 

Becky

Webmistress
Administrator
Joined
Mar 25, 2003
Messages
7,406
Reaction score
1,501
Welcome to the forum @RedMoggy! Great to have you here :)

I think this formula should do what you want:

=IF(A1/B1>=80%,"Distinction",IF(A1/B1>=60%,"Merit",IF(A1/B1>=40%,"Pass","Fail")))

...where A1 is the student's score, and B1 is the maximum score. Let me know if you want to know more about how this works, I'd be happy to explain it to you.
 
Joined
Jan 13, 2019
Messages
8
Reaction score
4
Welcome to the forum @RedMoggy! Great to have you here :)

I think this formula should do what you want:

=IF(A1/B1>=80%,"Distinction",IF(A1/B1>=60%,"Merit",IF(A1/B1>=40%,"Pass","Fail")))

...where A1 is the student's score, and B1 is the maximum score. Let me know if you want to know more about how this works, I'd be happy to explain it to you.
OH gosh will you marry me lol, Absolutely brilliant - It worked and it appears so easy now that you have shown me. I have struggled for hours with it. I am so grateful to you. It so hard when you are trying to teach yourself and you get stuck and have no one to show you how to do it and when you have a go and it doesnt work at all or part works and no one to show you were you have gone wrong.
 
Ad

Advertisements

Joined
Jan 13, 2019
Messages
8
Reaction score
4
Happy to help! :)
Name Part A Part B Part C
David 11 14 42 44% 56% 84% 67% Fail FALSE
Daniel 12 22 15 48% 88% 30% 49% Pass Pass
Davinia 10 13 29 40% 52% 58% 52% Fail FALSE
Dennis 13 15 30 52% 60% 60% 58% Pass Pass
Denise 20 21 43 80% 84% 86% 84% Pass Distinction
Damon 19 21 43 76% 84% 86% 83% Pass Distinction
Daniel 14 12 23 56% 48% 46% 49% Fail FALSE
Donna 21 15 41 84% 60% 82% 77% Pass Merit
Derrick 24 22 23 96% 88% 46% 69% Pass Merit
Darren 12 13 9 48% 52% 18% 34% Fail FALSE

=IF(AND($E91>=45%,$F91>=55%),IF($H91>=80%,"Distinction",IF($H91>=60%,"Merit",IF($H91>=40%,"Pass","Fail"))))

I added the percentage columns so that I could do the IF AND formula as shown above. The last but one column is where I did the first half on its own (as not certain if I had done it correctly) and the last column is both the 1st half of the IF formula and the 2nd half for the overall percentage and whether distinction, merit, pass or fail but instead of showing fail its returned a result of false - can you tell me where I have gone wrong.
 
Ad

Advertisements

Becky

Webmistress
Administrator
Joined
Mar 25, 2003
Messages
7,406
Reaction score
1,501
Name Part A Part B Part C
David 11 14 42 44% 56% 84% 67% Fail FALSE
Daniel 12 22 15 48% 88% 30% 49% Pass Pass
Davinia 10 13 29 40% 52% 58% 52% Fail FALSE
Dennis 13 15 30 52% 60% 60% 58% Pass Pass
Denise 20 21 43 80% 84% 86% 84% Pass Distinction
Damon 19 21 43 76% 84% 86% 83% Pass Distinction
Daniel 14 12 23 56% 48% 46% 49% Fail FALSE
Donna 21 15 41 84% 60% 82% 77% Pass Merit
Derrick 24 22 23 96% 88% 46% 69% Pass Merit
Darren 12 13 9 48% 52% 18% 34% Fail FALSE

=IF(AND($E91>=45%,$F91>=55%),IF($H91>=80%,"Distinction",IF($H91>=60%,"Merit",IF($H91>=40%,"Pass","Fail"))))

I added the percentage columns so that I could do the IF AND formula as shown above. The last but one column is where I did the first half on its own (as not certain if I had done it correctly) and the last column is both the 1st half of the IF formula and the 2nd half for the overall percentage and whether distinction, merit, pass or fail but instead of showing fail its returned a result of false - can you tell me where I have gone wrong.
Can you tell me a little more about the data - it looks like the first column is the name, the next three columns are the scores, and presumably the percentages are the score as a percentage. What is that last percentage? And could you describe how you want the last two columns to work?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

The Newbee 10
upriverjoe (newbee) 13

Top