Experienced in Excel?

Collapse

Recommended Videos

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • 500bloc
    MVP
    • May 2003
    • 1279

    #1

    Experienced in Excel?

    I'm working on a project and I cant seem to figure out the final steps on this spreadsheet i'm working on. Its fairly simple and I feel as if I'm on the cusp of the answer but cannot quite get it.

    If someone would be kind enough to lend a hand.

    I'm trying to convert Grades (A+, B-, D+, etc..) into Numbers, but my problem seems that some grades are converting correctly while others are not.


    My grade to number conversions are located on the second sheet, in the download below.


    I tried for help on another site but the explanation was a bit to complicated for me, maybe this could help someone.


    Spreadsheet
  • av7
    Hall Of Fame
    • Dec 2007
    • 11408

    #2
    Re: Experienced in Excel?

    Moved to neighborhood, you'll get more responses here
    Aaron
    Moderator

    Comment

    • Trevytrev11
      MVP
      • Nov 2006
      • 3259

      #3
      Re: Experienced in Excel?

      Try this formula:

      =VLOOKUP(H3,Sheet2!$A$3:$B$13,2,0).

      All this formula is saying is look up the grade in the table on sheet 2 and bring back the second column.

      It should work once you populate the number that corresponds to the letter grade on sheet 2.

      The $ will lock your range so it doesn't move when you copy your formula down your rows.

      Comment

      • 500bloc
        MVP
        • May 2003
        • 1279

        #4
        Re: Experienced in Excel?

        Originally posted by Trevytrev11
        Try this formula:

        =VLOOKUP(H3,Sheet2!$A$3:$B$13,2,0).

        All this formula is saying is look up the grade in the table on sheet 2 and bring back the second column.

        It should work once you populate the number that corresponds to the letter grade on sheet 2.

        The $ will lock your range so it doesn't move when you copy your formula down your rows.

        I entered that formula and got an Err:508.

        The letter grades on sheet 2 are in ascending order so I really don't know what I'm doing wrong. I know i'm close, just can't seem to figure out what it is.

        Comment

        • Trevytrev11
          MVP
          • Nov 2006
          • 3259

          #5
          Re: Experienced in Excel?

          Not sure. I downloaded your spreadsheet and it worked for me.

          Did you move any of the data?

          Comment

          • 500bloc
            MVP
            • May 2003
            • 1279

            #6
            Re: Experienced in Excel?

            Originally posted by Trevytrev11
            Not sure. I downloaded your spreadsheet and it worked for me.

            Did you move any of the data?
            Let me try it again.

            I appreciate u helping.


            Edit: I tried it again with the original that u downloaded and for some reason it still did not work, even as I copied and pasted it to the spreadsheet.
            Could possibly post the formula already inserted in Excel up for me?
            Last edited by 500bloc; 10-06-2009, 10:26 PM.

            Comment

            • Cardot
              I'm not on InstantFace.
              • Feb 2003
              • 6164

              #7
              Re: Experienced in Excel?

              I am looking at the file, and on sheet 2, column B you have a formula. Don't you want that to be the table that maps the grades to numbers?.

              I put numbers in column sheet 2, colum B.
              Then on sheet 1, I put Trevy's formula in column P. It worked for me.

              Comment

              • 500bloc
                MVP
                • May 2003
                • 1279

                #8
                Re: Experienced in Excel?

                Originally posted by Cardot
                I am looking at the file, and on sheet 2, column B you have a formula. Don't you want that to be the table that maps the grades to numbers?.
                Yes, I would like the Sheet 1 "H" column (specifically H3), to be the grade i would imput, and Sheet 1 "P" column to be the output.


                Originally posted by Cardot
                I put numbers in column sheet 2, colum B. Then on sheet 1, I put Trevy's formula in column P. It worked for me.
                And thats what gets me, when I try for some reason, I have less than favorable results, and cannot seem to get why.

                Thats why I would be most grateful if you or Trevytrev11 could post the spreadsheet with the formula added in, cause I'm just not getting it.

                Comment

                • Cardot
                  I'm not on InstantFace.
                  • Feb 2003
                  • 6164

                  #9
                  Re: Experienced in Excel?

                  What number should all the letters be? Like is "A+" equal to 100? Is "A" 95 ???

                  Comment

                  • 500bloc
                    MVP
                    • May 2003
                    • 1279

                    #10
                    Re: Experienced in Excel?

                    Originally posted by Cardot
                    What number should all the letters be? Like is "A+" equal to 100? Is "A" 95 ???
                    <meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8"><title></title><meta name="GENERATOR" content="OpenOffice.org 3.1 (Win32)"><style> <!-- BODY,DIV,TABLE,THEAD,TBODY,TFOOT,TR,TH,TD,P { font-family:"Arial"; font-size:x-small } --> </style> <table border="0" cellspacing="0" cols="2" frame="VOID" rules="NONE"> <colgroup><col width="86"><col width="140"></colgroup> <tbody> <tr> <td align="CENTER" height="17" width="86">A+</td> <td sdval="98.992" sdnum="1033;0;00" align="CENTER" width="140">99</td> </tr> <tr> <td align="CENTER" height="17">A</td> <td sdval="92.826" sdnum="1033;0;00" align="CENTER">93</td> </tr> <tr> <td align="CENTER" height="17">A-</td> <td sdval="86.66" sdnum="1033;0;00" align="CENTER">87</td> </tr> <tr> <td align="CENTER" height="17">B+</td> <td sdval="80.494" sdnum="1033;0;00" align="CENTER">80</td> </tr> <tr> <td align="CENTER" height="17">B</td> <td sdval="74.328" sdnum="1033;0;00" align="CENTER">74</td> </tr> <tr> <td align="CENTER" height="17">B-</td> <td sdval="68.162" sdnum="1033;0;00" align="CENTER">68</td> </tr> <tr> <td align="CENTER" height="17">C+</td> <td sdval="61.996" sdnum="1033;0;00" align="CENTER">62</td> </tr> <tr> <td align="CENTER" height="17">C</td> <td sdval="55.83" sdnum="1033;0;00" align="CENTER">56</td> </tr> <tr> <td align="CENTER" height="17">C-</td> <td sdval="49.664" sdnum="1033;0;00" align="CENTER">50</td> </tr> <tr> <td align="CENTER" height="17">D+</td> <td sdval="43.498" sdnum="1033;0;00" align="CENTER">43</td> </tr> <tr> <td align="CENTER" height="17">D</td> <td sdval="37.332" sdnum="1033;0;00" align="CENTER">37</td> </tr> <tr> <td align="CENTER" height="17">D-</td> <td sdval="31.166" sdnum="1033;0;00" align="CENTER">31</td> </tr> <tr> <td align="CENTER" height="17">F</td> <td sdval="25" sdnum="1033;0;00" align="CENTER">25</td> </tr> </tbody> </table>
                    What I wanted to see is that whenever I imput a letter grade (i.e. C+), I would see a numerical value for it (i.e. 62).

                    Comment

                    • Cardot
                      I'm not on InstantFace.
                      • Feb 2003
                      • 6164

                      #11
                      Re: Experienced in Excel?

                      I am at work now, so I can't download the file. But I will tonight.....Unless Trevy beats me to it.

                      Comment

                      • Trevytrev11
                        MVP
                        • Nov 2006
                        • 3259

                        #12
                        Re: Experienced in Excel?

                        500bloc, Check your PM.

                        Comment

                        • Cardot
                          I'm not on InstantFace.
                          • Feb 2003
                          • 6164

                          #13
                          Re: Experienced in Excel?

                          Originally posted by Trevytrev11
                          500bloc, Check your PM.
                          Show off. J/K

                          Comment

                          • 500bloc
                            MVP
                            • May 2003
                            • 1279

                            #14
                            Re: Experienced in Excel?

                            Thanks Trevytrev11 for the formula & thank you also Cardot.

                            I didn't know if i was ever going to get that done.

                            Comment

                            • Cardot
                              I'm not on InstantFace.
                              • Feb 2003
                              • 6164

                              #15
                              Re: Experienced in Excel?

                              Originally posted by 500bloc
                              Thanks Trevytrev11 for the formula & thank you also Cardot.

                              I didn't know if i was ever going to get that done.
                              Now it looks like the real work is ahead of you...evaluating players in all those categories!

                              Comment

                              Working...