+ Reply to Thread
Results 1 to 9 of 9

Convert alpha to numeric & then create formula - Thanks for reading

  1. #1
    Registered User
    Join Date
    07-18-2013
    Location
    US, Boston
    MS-Off Ver
    Excel 2007
    Posts
    3

    Convert alpha to numeric & then create formula - Thanks for reading

    Hello to everyone. I am creating a spreadsheet for work. Columns will have rankings of Very High to Very Low for end users to complete; would like to keep the cells as text responses but in the formula they would represent 1:5 with VH = 5 points and so on. However, I then want to create a formula to give an overall rating to those rankings. Please see attached and I can be contacted for more information, if needed. I welcome any help with this. I tried the COUNTIF but that only gave to me a SUM of the row across after successfully assigning a number value to each alphabet combination....not exactly what I needed, but close!

    Kind regards,
    Ny

    Convert Text to Numbers - Copy.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Convert alpha to numeric & then create formula - Thanks for reading

    Hi and welcome to the forum

    Im sure some-one will give you something more elegant, but you can use this in the mean time...

    =SUM(COUNTIF(D6:D10,"VH")*5,COUNTIF(D6:D10,"H")*4,COUNTIF(D6:D10,"M")*3,COUNTIF(D6:D10,"L")*2,COUNTIF(D6:D10,"VL"))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: Convert alpha to numeric & then create formula - Thanks for reading

    Here you go, I had to modify your sheet slightly.
    Attached Files Attached Files

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert alpha to numeric & then create formula - Thanks for reading

    Create a 2 column table like this:

    VH.....5
    H.......4
    M......3
    L.......2
    VL.....1

    Let's assume that table is in the range A6:B10.

    Enter this formula in G6 and copy down:

    =(VLOOKUP(D6,A$6:B$10,2,0)+VLOOKUP(E6,A$6:B$10,2,0))/(2+VLOOKUP(F6,A$6:B$10,2,0))

    Note that in your table some of the cells contain trailing space characters!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    07-18-2013
    Location
    US, Boston
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Convert alpha to numeric & then create formula - Thanks for reading

    Hello @Tony Valko, thank you for your response. When I did as described, my results in Overall Rating column are :
    0.857142857
    1
    1.2
    #N/A
    2

    In manual calculations, the results going down should be 8, 7, 6, 5, 4. Is it my "trailing space characters" that is causing this issue?!

    Kind regards,
    Ny !

    EDIT : I have removed the begin paren in formula at the "2" after the division. This seems to have corrected the formula but the #N/A for Row 9 remains. I'm still working on that....
    Last edited by NylaTai; 07-18-2013 at 12:14 PM.

  6. #6
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: Convert alpha to numeric & then create formula - Thanks for reading

    Have you tried my solution?

  7. #7
    Registered User
    Join Date
    07-18-2013
    Location
    US, Boston
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Convert alpha to numeric & then create formula - Thanks for reading

    Hi everyone, I'd like to thank you for your quick responses and help! I think it's been solved now using Tony Valko's formula.

    When I used the formula above from FDibbins, it gave to me Sum of columns going down which is similar to what I got using CountIF going across the row. Not exactly what I needed...

    When I checked igoodable sheet that was modified, the formula provided incorrect sum versus manual calculations. The answers using this formula going down in Overall Rating column were: VH, M, M, M , M (meaning: 5, 3, 3, 3, 3) when they should have been 8,7, 6, 5, 4

    I ran the formula from Tony Valko with a slight correction and it seems to be working wonderfully. I plugged the same formula into my working copy and am happy to report that it checks out fine!

    Many thanks to all of you. I will add "Solved" to the title post. I appreciate all of your help. Have a nice day!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Convert alpha to numeric & then create formula - Thanks for reading

    Happy to help

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert alpha to numeric & then create formula - Thanks for reading

    You're welcome. We appreciate the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert alpha-numeric code to date
    By Buchu in forum Excel General
    Replies: 13
    Last Post: 06-04-2012, 12:51 PM
  2. Convert alpha-numeric to numeric
    By michael mac in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-12-2008, 04:13 PM
  3. Replies: 1
    Last Post: 11-28-2006, 02:42 AM
  4. [SOLVED] Convert Alpha to Numeric
    By Vicki in forum Excel General
    Replies: 2
    Last Post: 03-21-2005, 08:06 AM
  5. [SOLVED] convert cells containing alpha to numeric
    By ezu in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-09-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1