+ Reply to Thread
Results 1 to 20 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
    70

    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 Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

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

    got it Wrong

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,342

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,342

    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
    70

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,342

    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
    70

    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 Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    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
    70

    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
    70

    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
    70

    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.

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

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

    Oh, by the way, I marked this thread as solved but was wondering if you know of a format that matches the padding of the Accounting format. I explain this further in original post and in my last reply to Fluff13.

  17. #17
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

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

    You're welcome & thanks for the feedback.

    To add a small space to the right of the number you can use this custom format
    0 ;;;

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

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

    Very cool! That works perfectly for the right margin. Is there some other great magic trick for adding space to the left. If you look at the form I uploaded, the Creditor column is formatted to Accounting precisely because of the space it provides so that the creditor name is not tight to the border. If there is a way to add a (left) space to that column, then I can also set those cells to go to blank instead of a dash.

    Figured it out!
    So, here's how I did it. The cells were formatted to accounting as mentioned (no $, no decimal place). Then I clicked on Custom and this was display _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_). I then removed the dash in third bracket set, that did the trick.
    Last edited by BryanHCR; 05-21-2019 at 02:12 PM.

  19. #19
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

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

    Glad you figured it out & thanks for the feedback

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

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

    Yes indeed...thanks to the help people like you provided in the forum. The debt-form now does everything I wanted it to do, which feels great, and I learned a bunch of things too!

    Thanks again!

+ 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. If Formula for blank condition
    By shiva_reshs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-03-2014, 01:25 PM
  2. Formula to display workday days with condition
    By shiva_reshs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-11-2014, 03:04 PM
  3. VBA to display msg box when condition is met in cell
    By shiva_reshs in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-14-2013, 09:05 AM
  4. [SOLVED] Need to know if a cell is blank if a certain condition is met.
    By Jowel in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-05-2013, 03:43 AM
  5. [SOLVED] Help for if condition formula result only either hit or miss from mulitple condition
    By breadwinner in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2013, 07:29 AM
  6. [SOLVED] Display Blank If Condition Is Met
    By nathanB in forum Excel General
    Replies: 2
    Last Post: 01-17-2013, 07:48 AM
  7. [SOLVED] Display Image on Condition of Cell Value
    By ligerdub in forum Excel General
    Replies: 5
    Last Post: 06-29-2012, 10:01 AM

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