Newbee

Discussion in 'Introductions' started by RedMoggy, Jan 13, 2019.

  1. RedMoggy

    RedMoggy

    Joined:
    Jan 13, 2019
    Likes Received:
    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
     
    RedMoggy, Jan 13, 2019
    #1
    Abraham Andres Luna likes this.
    1. Advertisements

  2. RedMoggy

    nivrip Yorkshire Cruncher

    Joined:
    Mar 21, 2007
    Likes Received:
    1,416
    Location:
    North Yorkshire
    Hi RedMoggy and welcome to PCR. :wave:

    Not my field but there may be someone along shortly who can help. :)
     
    nivrip, Jan 13, 2019
    #2
    Abraham Andres Luna likes this.
    1. Advertisements

  3. RedMoggy

    RedMoggy

    Joined:
    Jan 13, 2019
    Likes Received:
    4
    Thank you for the welcome
     
    RedMoggy, Jan 13, 2019
    #3
  4. RedMoggy

    Abraham Andres Luna

    Joined:
    Mar 14, 2018
    Likes Received:
    129
    Location:
    Spring Hill, FL 34608
    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, Jan 13, 2019
    #4
  5. RedMoggy

    RedMoggy

    Joined:
    Jan 13, 2019
    Likes Received:
    4

    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).
     
    RedMoggy, Jan 13, 2019
    #5
  6. RedMoggy

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,446
    Location:
    Manchester
    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, Jan 13, 2019
    #6
  7. RedMoggy

    RedMoggy

    Joined:
    Jan 13, 2019
    Likes Received:
    4
    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.
     
    RedMoggy, Jan 13, 2019
    #7
    Abraham Andres Luna and Becky like this.
  8. RedMoggy

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,446
    Location:
    Manchester
    Happy to help! :)
     
    Becky, Jan 13, 2019
    #8
  9. RedMoggy

    RedMoggy

    Joined:
    Jan 13, 2019
    Likes Received:
    4
    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, Jan 13, 2019
    #9
  10. RedMoggy

    nivrip Yorkshire Cruncher

    Joined:
    Mar 21, 2007
    Likes Received:
    1,416
    Location:
    North Yorkshire

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

    Not sure what Ian will say about it though. :D
     
    nivrip, Jan 13, 2019
    #10
    Becky and Abraham Andres Luna like this.
  11. RedMoggy

    RedMoggy

    Joined:
    Jan 13, 2019
    Likes Received:
    4
    Well I was after her mind not her body lol
     
    RedMoggy, Jan 13, 2019
    #11
    Becky likes this.
  12. RedMoggy

    Abraham Andres Luna

    Joined:
    Mar 14, 2018
    Likes Received:
    129
    Location:
    Spring Hill, FL 34608
    Guess I went the wrong route on that one. @RedMoggy said VBA so I thought he was doing it that way.
     
    Abraham Andres Luna, Jan 13, 2019
    #12
  13. RedMoggy

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,446
    Location:
    Manchester
    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?
     
    Becky, Jan 14, 2019
    #13
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.
Similar Threads
Loading...