Newbee

[RedMoggy]

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]

Hi RedMoggy and welcome to PCR.

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

 

[RedMoggy]

Hi RedMoggy and welcome to PCR.

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

Thank you for the welcome

 

[Abraham Andres Luna]

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.

 

[RedMoggy]

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.

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]

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.

 

[RedMoggy]

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.

 

[Becky]

Happy to help!

 

[RedMoggy]

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.

 

[nivrip]

OH gosh will you marry me lol, Absolutely brilliant

Wow !! Not often that someone gets a proposal of marriage on this forum.

Not sure what Ian will say about it though.

 

[RedMoggy]

Wow !! Not often that someone gets a proposal of marriage on this forum.

Not sure what Ian will say about it though.

Well I was after her mind not her body lol

 

[Abraham Andres Luna]

Guess I went the wrong route on that one. @RedMoggy said VBA so I thought he was doing it that way.

 

[Becky]

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?