+ Reply to Thread
Results 1 to 6 of 6

give n/a value

  1. #1
    Registered User
    Join Date
    06-09-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    give n/a value

    I am trying to calculate multiple cells to get percentage.

    How would i give n/a cells a value or ignore n/a so that i still get a correct percentage. how can i keep n/a to show in the cell? Where would i plug formula in?

    Some N/A value could be 1 and some could be 5


    A B C D E F G H I J Score
    5 5 5 5 n/a 5 5 5 5 na 100%


    Thanks in advance

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: give n/a value

    Assuming "n/a" is text then

    =SUMIF(A1:J1,"<>" & "n/a",A1:J1) will sum the values. I don't what the calculation is for your %


    similarly =COUNTIF(A1:J1,"=" & 5) will count cells containing 5

    Hope this helps

  3. #3
    Registered User
    Join Date
    06-09-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Re: give n/a value

    when i try that formula, it only ignores the n/a. I need it to count n/a as a value

    For example the top row would have the value n/a.
    5 5 5 5 5 5 5 5 5 5 1 1 Total=52 100%
    5 5 5 5 5 5 5 n/a 5 5 n/a 1 Total=52 100%

    So sorry its a bit confusing, i know.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: give n/a value

    =SUMIF(A1:J1,"<>" & "n/a",A1:J1)+COUNTIF(A1:J1,"=" & "n/a") assumes "n/a" has value of 1

    OR

    =SUMIF(A1:J1,"<>" & "n/a",A1:J1)+COUNTIF(A1:J1,"=" & "n/a")*A1 where A1 contains a value foe "n/a"

  5. #5
    Registered User
    Join Date
    06-09-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Re: give n/a value

    That wont work

    i did find something similar online but cannot get the formula to work. it results in error

    I am trying to add a formula that will give "n/a" in a cell a value based on info in header.

    The formula i found in google forum is

    =sum(arrayformula(if(C9:C33="N/A",value(mid(A9:A33,2,search(")",A9:A33)-2)),C9:C33)))
    When entered in Excel 2010 i get #NAME?

    This is the link to what my spreadsheet looks like.

    https://docs.google.com/spreadsheets...it?usp=sharing

    This is the link to where i got the formula. They are creating a similar worksheet.

    https://docs.google.com/spreadsheets...it?usp=sharing

    I hope this explains it better lol
    Thanks for all the help
    Last edited by cmonstuh; 06-23-2015 at 03:11 PM.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: give n/a value

    What value did you assume for n/a in this example

    5 5 5 5 5 5 5 n/a 5 5 n/a 1 Total=52 100% logic suggests 3 (9*5 + 1 +2*3)

    using this with Q1=3 gives result of 52

    =SUMIF(A1:L1,"<>" & "n/a",A1:L1)+COUNTIF(A1:L1,"=" & "n/a")*Q1

+ 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. [SOLVED] If Statements (?) - If Cell1 =yes then give -Cell2, If Cell1=no then give +Cell2
    By KTXD in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-02-2012, 12:24 AM
  2. when and how do you give up with an OP
    By tony h in forum The Water Cooler
    Replies: 26
    Last Post: 05-03-2010, 10:27 PM
  3. Give RELEVANT responses to questions. DO NOT give usless list
    By pmartin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-06-2006, 02:00 PM
  4. [SOLVED] Give help
    By heinze22 in forum Excel General
    Replies: 2
    Last Post: 04-26-2006, 09:55 AM

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