Would like to input 3 letters into a cell, each having a different numerical value

Discussion in 'Excel' started by jdoon5261, Oct 2, 2016.

  1. jdoon5261

    jdoon5261

    Joined:
    Oct 2, 2016
    Messages:
    4
    Likes Received:
    0
    Hi Everyone,
    New to the forum. I'm a CATE teacher and am working on a professionalism rubric that I can carry with me in the lab. I am getting stuck on this part. I would like to input one of 3 letters into a cell and have it correspond to a numerical value that a formula in another cell will see as that numerical value.
    A layman's example would be: Y=100, P=75, n=50. I have this formula "=SUM(F2*0.25)+(((G2+H2)/2)*0.25)+(((J2+K2)/2)*0.25)+(((M2+N2)/2)*0.25)" in another cell that would need the numerical value of the letter in order for it to work.
    I know my formula is somewhat amateurish but I'm a noob to all of this.
    Thanks
    John.
     
    jdoon5261, Oct 2, 2016
    #1
    1. Advertisements

  2. jdoon5261

    Becky Administrator

    Joined:
    Aug 3, 2011
    Messages:
    42
    Likes Received:
    4
    Welcome to the forum :)

    Could you talk me through your formula please, and also give some details on what isn't working?
     
    Becky, Oct 3, 2016
    #2
    1. Advertisements

  3. jdoon5261

    jdoon5261

    Joined:
    Oct 2, 2016
    Messages:
    4
    Likes Received:
    0
    Hi Becky,
    Sorry but I accidentally deleted the link to this site.
    I've been working on my worksheet and have learned quite a bit since October. I'm still running into an issue with the original problem that I posted above. I should be better able to explain it now though.
    My goal is to input 1 of 3 possible letter characters into a cell and have it represent a numerical value that can then be used in the formula of another cell. I've seen some examples like =IF("Y"=0, "N"=1) but I can't seem to make that work for me. I can only assume I'm doing something wrong.
    I hope you are still out there and can help me.
    Thanks.
    John
     
    jdoon5261, Mar 4, 2017
    #3
  4. jdoon5261

    jdoon5261

    Joined:
    Oct 2, 2016
    Messages:
    4
    Likes Received:
    0
    Wow, I remember why I kept putting this part of my worksheet off. Because it is kicking my butt.
    a pic might help.
    N4.JPG




    the area I'm talking about is the fields below the participation column. I would like the letters "Y", "E", and "V" to equal 100. And the letter "N" to equal 0. If the cell is left blank then no value should be returned.
    The formula in the N4 cell would then average out the values of the "I4" thru "I5" cells. The "N4" cell value is then used in an =average formula in cell "AA4"
    I hope this better explains it.
     
    jdoon5261, Mar 4, 2017
    #4
  5. jdoon5261

    jdoon5261

    Joined:
    Oct 2, 2016
    Messages:
    4
    Likes Received:
    0
    Is there a way to edit my posts?
     
    jdoon5261, Mar 4, 2017
    #5
  6. jdoon5261

    Becky Administrator

    Joined:
    Aug 3, 2011
    Messages:
    42
    Likes Received:
    4
    This would do what you want, but it's pretty messy! Could be simplified down but I'm pushed for time.

    =IF(I4<>"",VLOOKUP(I4,I6:J9,2,FALSE))+IF(J4<>"",VLOOKUP(J4,I6:J9,2,FALSE))+IF(K4<>"",VLOOKUP(K4,I6:J9,2,FALSE))+IF(L4<>"",VLOOKUP(L4,I6:J9,2,FALSE))+IF(M4<>"",VLOOKUP(M4,I6:J9,2,FALSE)))/COUNTA(I4:M4)

    Where cells I6:J9 is a table showing the corresponding values to the letters (you could put this anywhere in your workbook). Basically the formula looks at each day in turn, considers if there if there is data in the cell, and if so it looks up the corresponding value from the table. Then all values are added together, and divided by the number of days that data has been input for. In this example, the result is 75.

    excel value look up average.jpg

    Hope this is what you were looking for but if I've misunderstood please let me know!
     
    Becky, Mar 6, 2017
    #6
    1. Advertisements

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
  1. alabil

    Finding specific value

    alabil, Mar 7, 2012, in forum: Excel
    Replies:
    0
    Views:
    284
    alabil
    Mar 7, 2012
  2. Stormy4757
    Replies:
    1
    Views:
    758
    XLPadawan
    Jun 9, 2016
  3. Jorge
    Replies:
    0
    Views:
    578
    Jorge
    Aug 27, 2013
  4. Hwalker1
    Replies:
    2
    Views:
    557
    XLPadawan
    Jun 13, 2016
  5. Siegfried

    sum based on value in a range

    Siegfried, Jun 27, 2014, in forum: Excel
    Replies:
    0
    Views:
    628
    Siegfried
    Jun 27, 2014
  6. DanM
    Replies:
    1
    Views:
    403
    XLPadawan
    Jun 13, 2016
  7. hyukfgh
    Replies:
    2
    Views:
    636
    hyukfgh
    Oct 16, 2014
  8. Shivesh
    Replies:
    1
    Views:
    298
    XLPadawan
    Jun 14, 2016
Loading...