<>0 formula with blank cells
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: <>0 formula with blank cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default <>0 formula with blank cells

    Hi all, I have a formula that looks like this:

    =IFNA(IF(A3="","",IF(AX3<>0,AX3&" / ","")&IF(AY3>0,AY3&" / ","")&IF(AZ3<>0,AZ3&" / ","")&IF(BA3<>0,BA3&" / ","")&IF(BB3<>0,BB3&" / ","")&IF(BC3<>0,BC3&" / ","")&IF(BD3<>0,BD3&" / ","")&IF(BE3<>0,BE3,"")),"")

    The cells it is looking at - AX3,AY3,AZ3 etc. are all cells that contain a VLookup formula. Sometimes a value is displayed in these cells, sometimes it is blank (though not truly blank as it has the formula in it).

    All I want the formula I have to do is say 'If cell AX3 is not blank, provide the value in cell AX3 with a / at the end, otherwise keep it blank".

    However, it seems to pick up the 'blank' contents of of cell AX3, so it looks like this '/'.

    Anyone know what I am doing wrong?

  2. #2
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: <>0 formula with blank cells

    I checked the below code, if cell AX3 is blank, i am not getting SLASH("/") result but when is try this with (IF(ISBLANK) function i am getting SLASH("/") result.

    Code:
    =IFNA(IF(A3="","",IF(AX3<>0,AX3&" / ","")&IF(AY3>0,AY3&" / ","")&IF(AZ3<>0,AZ3&" / ","")&IF(BA3<>0,BA3&" / ","")&IF(BB3<>0,BB3&" / ","")&IF(BC3<>0,BC3&" / ","")&IF(BD3<>0,BD3&" / ","")&IF(BE3<>0,BE3,"")),"")
    Infact your above code seems pretty fine.
    Sam_D_Ben

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,385
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: <>0 formula with blank cells

    I believe that the adjustment to your formula simply requires all those <>0 parts of your formula to be changed to <>""
    So it would become
    =IFNA(IF(A3="","",IF(AX3<>"",AX3&" / ","")&IF(AY3<>"",AY3&" / ","")&IF(AZ3<>"",AZ3&" / ","")&IF(BA3<>"",BA3&" / ","")&IF(BB3<>"",BB3&" / ","")&IF(BC3<>"",BC3&" / ","")&IF(BD3<>"",BD3&" / ","")&IF(BE3<>"",BE3,"")),"")

    However, from looking at the last part of your formula, I am wondering if you are trying to avoid a " / " after whatever is the last value in the list. If that is so, and you have a recent version of Excel that has the TEXTJOIN function, you could do it with this much shorter formula.

    =TEXTJOIN(" / ",TRUE,AX3:BE3)
    Last edited by Peter_SSs; Aug 14th, 2019 at 12:46 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: <>0 formula with blank cells

    Perfect, many thanks Peter, the first part worked perfectly.

    With the TEXTJOIN part though; I don't understand what I need to amend within my formula - can you please show me?

    =IFNA(IF(A12="","",IF(AX12<>"",AX12&" / ","")&IF(AY12<>"",AY12&" / ","")&IF(AZ12<>"",AZ12&" / ","")&IF(BA12<>"",BA12&" / ","")&IF(BB12<>"",BB12&" / ","")&IF(BC12<>"",BC12&" / ","")&IF(BD12<>"",BD12&" / ","")&IF(BE12<>"",BE12,"")),"")

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,385
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: <>0 formula with blank cells

    Quote Originally Posted by NJS1982 View Post
    Perfect, many thanks Peter, the first part worked perfectly.
    Good news.


    Quote Originally Posted by NJS1982 View Post
    With the TEXTJOIN part though; I don't understand what I need to amend within my formula - can you please show me?
    If you have the TEXTJOIN function then try replacing that whole long formula with this one & see if it does what you want.

    =IF(A3="","",TEXTJOIN(" / ",TRUE,AX3:BE3))
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: <>0 formula with blank cells

    Great, many thanks!!

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,385
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: <>0 formula with blank cells

    Quote Originally Posted by NJS1982 View Post
    Great, many thanks!!
    You're welcome.

    Did you get the TEXTJOIN option going?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: <>0 formula with blank cells

    It did, thanks. I just had to add in some extra columns and hide my cell with the longer original formula, presenting the user with just the TEXTJOIN result. Thanks again!

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,385
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: <>0 formula with blank cells

    Quote Originally Posted by NJS1982 View Post
    It did, thanks. I just had to add in some extra columns and hide my cell with the longer original formula, presenting the user with just the TEXTJOIN result. Thanks again!
    I don't really understand that as the idea of the TEXTJOIN function was to completely replace the longer original formula.

    Anyway, if you have something that you are happy with that's fine.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: <>0 formula with blank cells

    Quote Originally Posted by Peter_SSs View Post
    I don't really understand that as the idea of the TEXTJOIN function was to completely replace the longer original formula.

    Anyway, if you have something that you are happy with that's fine.
    Ahhh, I think I was over complicating it! Taken another look this morning and you are right, goodbye big long formula and hello your snappy short one. Thanks!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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