1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formula

Discussion in 'Business Applications' started by AR1Redneck, Jan 7, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. AR1Redneck

    AR1Redneck Thread Starter

    Joined:
    Dec 13, 2002
    Messages:
    206
    Hi all, i am doing this project for my school work. Its a grading system project. Ok let me see if i can explain it good enough for you to understand.

    OK i have a spreadsheet with all the grades on it, with all of them weighteed properly. The percentaage comes out perfect. What i am looking to do is, under the percentage, put the letter grade. For example, A, A-, B+, B, B-, and etc... OK i have the intervals that the percentage has to be in order to be a A or B and so on, but how can i formulate that into a formula so that when i put the grades in, i can get the letter grade as well as the percentage.

    I have the formula for the percentage all complete, just need the one for the letter grade.

    I saw somewhere there is a formula that contains an interval or something that if i say C19=83-90, then it would out a B in the cell. Anyone know how that works? It didnt work if i just put that into the cell. Thanks

    Drew
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  4. AR1Redneck

    AR1Redneck Thread Starter

    Joined:
    Dec 13, 2002
    Messages:
    206
    i Downloaded the one off of your website, im reading it now, but not really grasping it, VLOOKUP? what does that stand for? If gives the formula =VLOOKUP(A2,MyLookup,2,FALSE)

    A2= the cell that my percent is in correct?
    MyLookUp= the range? this is where i get lost

    not sure about the rest, lol sorry

    Drew
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    The instructions are all there. Did you look at each worksheet?

    Make a small table on another worksheet, 2 columns:

    Col A
    Percent (start % of Grade)
    100
    90
    80
    70

    Col B
    Grade (corresponds to % above)
    A
    B
    C
    D

    Select the cells that contain 100 through 70 AND A through D
    (should be cells A2 through D4 for my example).

    After selecting, hit Insert-Name-Define

    Type: Mylookup or something like that, and hit enter.

    You've named your range.

    Okay, on Sheet 1 you've got names in col A, % in Col B and you want the grade to appear in col C. Your headings are in row 1.

    In cell C2, you'll put:

    =VLOOKUP(B2,MyLookup,2)

    B2 is where your % is, right? It'll go to the range called mylookup and it'll go to the 2nd column which is the grades, and return that value.

    In your case, you don't want the FALSE argument.

    If you can't get it to work, email your file: [email protected]
     
  6. AR1Redneck

    AR1Redneck Thread Starter

    Joined:
    Dec 13, 2002
    Messages:
    206
    you have mail, sorry
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Don't apologize for taking someone up on something they offered.

    :D

    There is no shame in learning, right??
    Oh GAWD! I hope not!!
    :rolleyes:
     
  8. RandyG

    RandyG

    Joined:
    Jun 26, 2000
    Messages:
    7,762
    Dreamboat's suggestion is probably exactly what you need, but there is another, more limited, choice. It's called nested IF statements

    =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"")

    The way this works is it checks the Cell (A1) and sees if it fulfills certain criteria. If it does fulfille the criteria, then it will display a result, and if it does not fulfill the criteria, it will check it against the next set of criteria.

    so, let's say the number in Cell A1 is 90, then by the formula I have above, an A would be displayed in whichever Cell I input that formula. Here's how it works:

    =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"")



    1. =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"") This will check to see if there is any value in the cell at all, and if there is, it will go to the validation stage. If there isn't, then it will just stay blank
    2. =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"") This then checks to see if the value of A1 is less then the number I have determined to be a failure (60 in this example). If the number were less than this value, then F would be displayed, otherwise it goes to the next IF statement
    3. =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"") IF the number were less than 70, it would display a D, but since it is not, it goes to the next IF statement
    4. =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"") If the number was less than 80, it would display a C, otherwise it will go to the next IF statement
    5. =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"") If the number were lower than 90, then it would display B, otherwise it will display an A
      [/list=1]


      Do you get how it works??? It is a bit bulky, but is great for small computations. The major limiting factor is that is gets confusing when dealing with many IF statemenst, but the most limiting factor is that you can only nestle 7 IF statements together in One total statement. That means if you wanted a more specified grading scale, for instance having +'s and -'s added to the grade, you would not be able to do it with the IF statements, but would have to do the vlookup that Dreamboat has helped you with.


      I have only provided you with this as an example of alternatives, and also because you are learning. Hopefully I have explained myself well enough. The IF statement is perfect for limited functions.
     
  9. phil-key

    phil-key

    Joined:
    May 3, 2000
    Messages:
    24
    You could also try the Excel "Choose" function. Which is sort of like a programming Case statement.

    CHOOSE(index_num,value1,value2,…)
    Index_num specifies which value argument is selected. Index_num must be a number between 1 and 29, or a formula or reference to a cell containing a number between 1 and 29.

    · If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
    · If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
    · If index_num is a fraction, it is truncated to the lowest integer before being used.

    Value1, value2, ¼ are 1 to 29 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or text.

    If your letter grades fall into this range structure:
    a=90-100
    b=80-89
    c=70-79
    d=60-69
    f<65

    You could use the following formula:
    =CHOOSE(TRUNC(A2/10),"F","F","F","F","F","D","C","B","A","A")

    In other words, divide the grade by 10, truncate the decimal and use that as the index to the letter grade.
     
  10. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    I apologize profusely.

    I did get a file from the Redneck and showed him how to do the vlookup and, as far as I know, that worked well.

    :)
     
  11. RandyG

    RandyG

    Joined:
    Jun 26, 2000
    Messages:
    7,762
    don't apologise, unless you're being a smartarse!!;)


    I knew as soon as I saw he emailed you that you'd sort it, but I only wanted him to know of alternatives.
     
  12. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Dork.

    When are you inviting me over??
    :D
     
  13. RandyG

    RandyG

    Joined:
    Jun 26, 2000
    Messages:
    7,762
    You're always welcome, but with that attitude, it'll be a spanking you'll be getting . . . but without all the fun connotations!!:D;)


    At least we'll be able to get a nice Irish Coffee into you afterwards, and a proper one at that! None of that horrible whipped cream garbage, especially not the stuff from a can!!!
     
  14. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    My mom was a "cook", Randy.

    I only make fresh whipped cream. :)
     
  15. AR1Redneck

    AR1Redneck Thread Starter

    Joined:
    Dec 13, 2002
    Messages:
    206
    lmao @ dreamboat and randy...

    Yes i did get it to work right, thanks for the help dreamboat. And also thanks to the others who offered suggestions, i will try them and see if they work also. Thanks

    Drew
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/111999

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice