+ Reply to Thread
Results 1 to 10 of 10

Subtracting non-blank cells only

  1. #1
    Registered User
    Join Date
    04-07-2017
    Location
    Santa Rosa Beach, Florida
    MS-Off Ver
    2016
    Posts
    30

    Subtracting non-blank cells only

    How do I say in C1 that I want =A1-B1 only where A1 and B1 both contain a date?

    I am tracking how long it takes for a task to get done w the simple formula of =A1-B1 in my C1 cell. A1 and B1 contain dates or are blank until the task on that line is done. I use the C column to create an average.

    I just discovered if A1 and B1 are both empty, I am getting a 0 in C1 which is throwing off my average calculation. I cannot just exclude 0 from the formula that averages the C column bc the result in C is often 0 since A1 and B1 are supposed to happen on the same date.

    I know it is something simple, but I hit a brain block. Help!

  2. #2
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Subtracting non-blank cells only

    =if(and(isblank(a1),isblank(b1)),"",a1-b1)

  3. #3
    Registered User
    Join Date
    04-07-2017
    Location
    Santa Rosa Beach, Florida
    MS-Off Ver
    2016
    Posts
    30

    Re: Subtracting non-blank cells only

    Spectacular!!! Thanks.

  4. #4
    Registered User
    Join Date
    04-07-2017
    Location
    Santa Rosa Beach, Florida
    MS-Off Ver
    2016
    Posts
    30

    Re: Subtracting non-blank cells only

    It does not work where one cell has a date and one cell does not. In that case, I am getting -43510 instead of the blank.

  5. #5
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Subtracting non-blank cells only

    my bad, im exhausted right now. Not the best solution but it gets it done.

    =IF(A1="","",IF(B1="","",A1-B1))

  6. #6
    Registered User
    Join Date
    04-07-2017
    Location
    Santa Rosa Beach, Florida
    MS-Off Ver
    2016
    Posts
    30

    Re: Subtracting non-blank cells only

    I found one online and it gives me #NAME? instead of a blank, but it cleans up my averages. Now I just need to figure out how to get an NA instead of the error message...


    =IF(AND(ISNUMBER(U28),ISNUMBER(R28)),U28-R28,”NA“)

  7. #7
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Subtracting non-blank cells only

    see my post #5

  8. #8
    Registered User
    Join Date
    04-07-2017
    Location
    Santa Rosa Beach, Florida
    MS-Off Ver
    2016
    Posts
    30

    Re: Subtracting non-blank cells only

    It works!!! Thank you. Hope you feel better.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Subtracting non-blank cells only

    A slight variation...
    =IF(or(A1="",B1=""),"",A1-B1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Subtracting non-blank cells only

    The reason you got a #NAME? error when trying to use the formula in post #6 is that the double quotes around NA look like they are so called curly or smart quotes. Replace them with standard (straight) double quotes . Now you have 3 equivalent formulas to choose from

+ 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. [SOLVED] Subtracting with blank cells
    By terry3218 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-22-2021, 07:05 PM
  2. Replies: 12
    Last Post: 05-29-2018, 05:11 PM
  3. Count blank cells in a range and contiguous blank cells also as single cells! Tricky One!
    By SebastianColombia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2015, 02:32 PM
  4. Replies: 0
    Last Post: 05-08-2012, 12:42 PM
  5. subtracting dates results in a big number when a date field is blank
    By duugg in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 07-23-2009, 11:26 AM
  6. Subtracting to a blank cell
    By jephrey_dal in forum Excel General
    Replies: 4
    Last Post: 05-27-2007, 10:40 PM
  7. Subtracting From 2 Cells
    By scw1217 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-04-2006, 08:56 PM

Tags for this Thread

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