Newbee

RedMoggy

RedMoggy

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
 
This web site is a great place to start: https://docs.microsoft.com/en-us/of...ence/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.
 
Abraham Andres Luna said:
This web site is a great place to start: https://docs.microsoft.com/en-us/of...ence/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.
Click to expand...


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).
 
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.
 
Becky said:
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.
Click to expand...

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.
 
Becky said:
Happy to help! :)
Click to expand...
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.
 
RedMoggy said:
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.
Click to expand...

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?
 
Back
Top