+ Reply to Thread
Results 1 to 7 of 7

Cell Format Query: How to show blank if nothing is input and show 0.00 if 0 is input?

Hybrid View

The_Snook Cell Format Query: How to... 09-04-2014, 04:28 PM
brokenbiscuits Re: Cell Format Query: How to... 09-04-2014, 04:47 PM
newdoverman Re: Cell Format Query: How to... 09-04-2014, 05:12 PM
The_Snook Re: Cell Format Query: How to... 09-06-2014, 09:00 AM
newdoverman Re: Cell Format Query: How to... 09-06-2014, 10:21 AM
The_Snook Re: Cell Format Query: How to... 09-06-2014, 10:27 AM
newdoverman Re: Cell Format Query: How to... 09-06-2014, 10:50 AM
  1. 09-04-2014, 04:28 PM #1
    The_Snook is offline
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    Excel 2021
    Posts
    1,013

    Cell Format Query: How to show blank if nothing is input and show 0.00 if 0 is input?

    Hi,

    I've created an expenditure form that departments will complete and return. What I'd like is a custom cell format for the total cell that is blank if nothing has been input but shows as 0.00 if they have input 0.

    The attached example shows what I'm trying to achieve.

    Thanks in advance,

    Snook
    Attached Files Attached Files
    Last edited by The_Snook; 09-06-2014 at 10:28 AM.
    Reply With QuoteRegister To Reply
  2. 09-04-2014, 04:47 PM #2
    brokenbiscuits is offline
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Cell Format Query: How to show blank if nothing is input and show 0.00 if 0 is input?

    =if(or(isblank(c4),isblank(d4)),"",sum(c4:d4))
    Reply With QuoteRegister To Reply
  3. 09-04-2014, 05:12 PM #3
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Cell Format Query: How to show blank if nothing is input and show 0.00 if 0 is input?

    Here is your workbook using the following formula and a change of format in column E:

    Formula: copy to clipboard 
    =IF(OR(ISBLANK(C4),ISBLANK(D4)),"",SUM(C4:D4))
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  4. 09-06-2014, 09:00 AM #4
    The_Snook is offline
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    Excel 2021
    Posts
    1,013

    Re: Cell Format Query: How to show blank if nothing is input and show 0.00 if 0 is input?

    Thanks.

    Is it possible to amend the formula so that it covers a range of cells rather than just checking if individual cells are blank?

    I've attached an updated workbook to illustrate what I mean.

    Thanks in advance,

    Snook
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 09-06-2014, 10:21 AM #5
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Cell Format Query: How to show blank if nothing is input and show 0.00 if 0 is input?

    This appears to work. Note the change in cell format for the total column from Custom to Number.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 09-06-2014, 10:27 AM #6
    The_Snook is offline
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    Excel 2021
    Posts
    1,013

    Re: Cell Format Query: How to show blank if nothing is input and show 0.00 if 0 is input?

    Thanks newdoverman, much appreciated!

    I'm surprised I couldn't solve it myself but everything I was trying just didn't seem to work.

    Thanks again.

    Snook
    Reply With QuoteRegister To Reply
  7. 09-06-2014, 10:50 AM #7
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Cell Format Query: How to show blank if nothing is input and show 0.00 if 0 is input?

    I just woke up and nothing was working between the ears for quite a while
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Use data from input box into a formula and show result in active cell
    By Abhi1687 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-13-2013, 05:35 PM
  2. [SOLVED] Show / Hide Sheets based off a cell input
    By evancharles in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-16-2012, 09:35 AM
  3. Show input box to edit a locked cell
    By ShogunPatch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-15-2012, 02:34 PM
  4. Show Asteriks in a cell; not actual input
    By ions in forum Excel General
    Replies: 1
    Last Post: 02-19-2009, 12:09 PM
  5. Show data in 2 different ways without having to input cell refs manually?
    By annieq in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-09-2007, 09:00 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1