+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 20

IF FORMULA to set condition for cell to display 0 when condition is met blank when not met

  1. #1
    Registered User
    Join Date
    04-23-2019
    Location
    U.S.
    MS-Off Ver
    Excel 2016
    Posts
    55

    IF FORMULA to set condition for cell to display 0 when condition is met blank when not met

    Hi,

    Note: Added attachemnt to original post; I isolated the form shown in the pic and created a spreadsheet to make it easier for those helping me to try their formulas. : )

    In the attached screen pic I have cells I31-K31 bordered in blue. The formula for the cells in column K is simply (for example in row 31) =J-31-I31. I have those cells set to Custom format 0;;; so that they display blank when the value is 0 or when the cell is blank, which is perfect for when there is no data in the cells (as is the case for rows 38-43). However, in the rare instances where the payoff months will be the same, as is the case for row 31, I'd like those cells to display 0 and not blank, so I need the IF formula that would meet the condition for that to take place. Any help on this would be greatly appreciated.

    I was also wondering if there is a custom format that provides the same left and right cell padding as the Accounting format. So far, it seems all other formats are right up against the cell border unless you have the cell/s set to center or use the indent arrows which indent further than I would like.

    Thanks
    Bryan
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by BryanHCR; 05-21-2019 at 12:40 PM.

  2. #2
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    4,164

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    got it Wrong

  3. #3
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    4,164

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Using Conditional formatting, use this formula
    =AND(J2-I2=0,J2<>"")
    and set the format to number format

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    26,180

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Try this:

    =IF(AND(J31=I31,J31<>""),0,"")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    04-23-2019
    Location
    U.S.
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Thanks Ali, but that doesn't work. In order for that formula to display 0, I had to set the Number format to general, when I do the same for the rows with no data, they also display 0.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    26,180

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Try this:

    =IF(AND(J31=I31,J31<>""),"0","")

    Failing that, I would recommend that you deal with your 0s via the formulae you are using instead of using formatting.

  7. #7
    Registered User
    Join Date
    04-23-2019
    Location
    U.S.
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Hi, I'm not sure what you are referring to re "Failing that". And I agree, I would prefer to use a formula rather than conditional formatting.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    26,180

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Failing the second formula I gave you - did you try it?

    The formatting you are using is custom cell formatting, not conditional formatting.

  9. #9
    Registered User
    Join Date
    04-23-2019
    Location
    U.S.
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Hi, I hadn't yet tried your second formula because (until I took a closer look) it looked the same as the first so I thought you had just changed the message underneath it. Since then, I have tried it, and it also doesn't work.

    I have now isolated the form so, if you like, you can try your suggestions on the form.

    Also, I was aware I was using custom cell formatting and not conditional formatting. It was Fluff 13 who suggested "conditional formatting" with his formula. I'm not sure if his formula is actually "conditional formating". My experience with conditional formating is clicking the conditional formatting icon and having fonts and cells change colors based on the rules I set.

    Thanks again for you interest!

    PS the added form spreadsheet is in the original post.
    Last edited by BryanHCR; 05-21-2019 at 12:55 PM.

  10. #10
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    4,164

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Whilst I know you prefer not to use CF,
    with your sample file the formula is
    =AND(G4=H4,H4<>0)
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Try setting the column to number format, and use this formula:
    Please Login or Register  to view this content.
    Adjust the number formatting to suit your needs. Does that give you the results you're looking for?
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  12. #12
    Registered User
    Join Date
    04-23-2019
    Location
    U.S.
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Great, that works! Thank you!

    The reason I didn't want to use conditional formatting is because someone on the form said it takes more processing. However, this is just a small area so I don't anticipate any noticeable delays.

    Also, I was wondering if you have an answer to what I added to the original post (I think before you first read it) to my query re is there a custom format (or any format) that adds the left & right padding to the displayed numbers that matches the Accounting format. If so, I could change the current cells formatted to Accounting (precisely because I prefer that "padding look") to a similiar padding look and have those cells display blank instead of the dash which is a cleaner look.

    Thanks again for your help!

  13. #13
    Registered User
    Join Date
    04-23-2019
    Location
    U.S.
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Sorry duplicate reply, confused as reply wasn't directly under you post

  14. #14
    Registered User
    Join Date
    04-23-2019
    Location
    U.S.
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Mr Melvosh! good to hear from you. Your formula does the trick without the need for conditional formatting. Thank you!

  15. #15
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: IF FORMULA to set condition for cell to display 0 when condition is met blank when not

    Glad to know it works Looks like your project is shaping up nicely.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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