Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Winston-Salem, NC
    Posts
    25

    Unanswered: Formula that reads N/A as 0

    Hopefully someone can help me. I have 6 cells that look like:

    A B C D E F
    10 5 20 10 N/A N/A

    and a formula in cell seven that reads:
    =(B1+D1+F1) / (A1+C1+E1) -1

    The thing is the N/A may be in different cells (in column B on the next line, etc. ) The formula will always be in column 7 and the data will always be in columns 1-6. What I want to do is have the formula treat the N/A as 0. I want N/A in the cells, but I just want the formula to treat the N/A as 0. I was thinking something like =IF(A1=N/A,0,?) I want the ? to equal whatever the number is in the cell is. I don't know--I'm lost. Any help would be greatly appreciated.

    Thanks,
    Jeff

  2. #2
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    How about:

    =If(ISNA((B1+D1+F1)/(A1+C1+E1)-1),0,(B1+D1+F1)/(A1+C1+E1)-1))

    Hope that helps,

    Smitty

Posting Permissions

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