Results 1 to 6 of 6

Thread: If formula to return a different value if a cell has 0 or is blank
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If formula to return a different value if a cell has 0 or is blank

    Hi experts,
    I am new to the site, so I apologize if I have posted this in the incorrect area.

    In the below table I have the following formula in C3-C5,

    =IF(B3=1,$D$2,IF(B3=2,$E$2,IF(B3=3,$F$2,IF(B3=4,$G$2))))

    A1 B C D E F G
    2 Start Pause Re-set Stop
    3 1 Start 1 0 1 1
    4 4 Stop 1 1 1 1
    5 3 Re-set 1 0 0 1


    As you can see if I change the number in B3-B5 the value in C3-C5 changes.

    If I change B3 to 2, C3 changes to Pause.

    I am trying to come up with a formula if I change B3 to 2, it can see E3 is 0 and it automatically changes to the next cell that contains a value other than 0 in this example it would be F2 (Re-set).

    If I was to use B5 as the example and entered 2 in B5 it would return Stop as E5 and F5 both have 0.

    I can substitute the 0 to blank cells if that makes it easier.

    Thanks,
    Gavin

  2. #2
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,675
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: If formula to return a different value if a cell has 0 or is blank

    =IF(LEN(A1)=0,"The cell has nothing in it",IF(A1=0,"The cell has zero in it","The cell has something else in it"))
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If formula to return a different value if a cell has 0 or is blank

    Hi John,

    Thanks for the reply, that didn't work how I want it.

    I'm not even sure I can achieve what I want using a IF formula.

    I want to be able to manually change the number in column B and the text in column C changes depending on the numerical value in D,
    If D is blank it will look in E then F till a value is found, then returns the text in in headed in either E or F as a number is found.

    I hope that makes sense.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: If formula to return a different value if a cell has 0 or is blank

    Welcome to the MrExcel board!

    Formula in C3 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.
    If you prefer not to have to use the Ctrl+Shift+Enter, I have provided an alternative normal-entry formula in column J

    Next Value

    BCDEFGHIJ
    2 StartPauseRe-setStop
    32Re-set1011 Re-set
    43Re-set1111 Re-set
    52Stop1001 Stop

    Spreadsheet Formulas
    CellFormula
    C3{=INDEX(D$2:G$2,MATCH(TRUE,(D3:G3<>0)*(COLUMN(D3:G3)-COLUMN(D3)+1)>=B3,0))}
    J3=INDEX(D$2:G$2,MATCH(TRUE,INDEX((D3:G3<>0)*(COLUMN(D3:G3)-COLUMN(D3)+1)>=B3,0),0))
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    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 # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If formula to return a different value if a cell has 0 or is blank

    Thanks Peter,

    Worked perfectly thanks for your help.

    Gavin

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: If formula to return a different value if a cell has 0 or is blank

    Quote Originally Posted by GSY01 View Post
    Thanks Peter,

    Worked perfectly thanks for your help.

    Gavin
    You're welcome. Thanks for the follow-up.
    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 # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •