+ Reply to Thread
Results 1 to 13 of 13

How to assign numerical values to text?

  1. #1
    Registered User
    Join Date
    10-25-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    14

    How to assign numerical values to text?

    Hi,

    I have a data validation that has 5,3 and N/A. But when I pick N/A in the dropdown menu the sum and average reads it a zero How do I assign N/A as still being 5 so it doesn't change the average later on?

    Example:

    Untitled.png

    I want N/A to reflect as the maximum value in each drop down or for the formulas to ignore N/A and still calculate 100%


    Thanks in Advance.
    Last edited by Kastalarial; 12-09-2013 at 03:12 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to assign numerical values to text?


    A
    B
    1
    Data
    Data
    2
    5
    5
    3
    3
    3
    4
    5
    N/A
    5
    4.33
    4.33
    Last edited by :) Sixthsense :); 12-09-2013 at 03:13 AM.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to assign numerical values to text?


    a
    b
    5
    =average(a2:a4)
    =sum(b2:b4,countif(b2:b4,"n/a")*5)/(count(b2:b4)+countif(b2:b4,"n/a"))

  4. #4
    Registered User
    Join Date
    10-25-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: How to assign numerical values to text?

    This works, but I want a value set to maximum value to each the N/As eg. 5, so when the total score reads scores with N/A it will still calculate as 85% instead of deducting the N/As. Please see attached.

    Untitled.png

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to assign numerical values to text?

    Just remove the last part of countif and try like this..

    =sum(b2:b4,countif(b2:b4,"n/a")*5)/count(b2:b4)

  6. #6
    Registered User
    Join Date
    10-25-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: How to assign numerical values to text?

    I tried it and got these results. Untitled.png Is there a way to mark N/As with a specific value each time I select them?

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to assign numerical values to text?

    Quote Originally Posted by Kastalarial View Post
    Is there a way to mark N/As with a specific value each time I select them?
    Not sure what do you mean by the words "Each Time I Select Them"

  8. #8
    Registered User
    Join Date
    10-25-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: How to assign numerical values to text?

    I made it work. But what if I have different values?Untitled.png Like from C8-C36 with some cells as 3, 5 and 2?

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to assign numerical values to text?

    Sorry I really unable to understand your requirement

  10. #10
    Registered User
    Join Date
    10-25-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: How to assign numerical values to text?

    I have drop downs that have set 3, 5 and 2, 0 and N/A. Once added without N/A they calculate 100%. How do I ignore N/As to be able to still get the correct percentage? They really need not to be included in the calculation. So let's say C6 = 5, which is the maximum then C7 is 0 makes it 50%, but if I choose C8 as N/A it makes 33.33%. What I want to do is void N/As so when I select C6= 5 then C7 = 0 then C8 = N/A it will still be 50% respectively?

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to assign numerical values to text?


    c
    6
    5
    7
    0
    8
    n/a
    9
    50.00%



    c
    9
    =average(c6:c8)/sum(c6:c8)

  12. #12
    Registered User
    Join Date
    10-25-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: How to assign numerical values to text?

    Thanks it worked!

  13. #13
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to assign numerical values to text?

    Glad it helps you and thanks for the feedback and rep

+ 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. Assign Numerical Value to a Text String
    By getblended in forum Excel General
    Replies: 3
    Last Post: 01-12-2012, 11:40 AM
  2. Assign text to a numerical range.
    By jaspercoGIS in forum Excel General
    Replies: 2
    Last Post: 01-18-2008, 03:46 PM
  3. [SOLVED] How do I assign numerical values to a letter? Ex: A = 4, B=3, etc
    By Bob1023 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-25-2006, 09:55 PM
  4. [SOLVED] ASSIGN NUMERICAL VALUES FOR LETTERS in EXCEL 2003
    By legman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-13-2006, 11:15 PM
  5. [SOLVED] Assign numerical values to text codes
    By Bridget in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-18-2005, 07:06 PM

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