+ Reply to Thread
Results 1 to 4 of 4

modifying this formula to return an empty cell

  1. #1
    Outbacker
    Guest

    modifying this formula to return an empty cell

    the formula is
    =IF(OR(A1="company1",A1="company2"),B1+3,IF(OR(A1="company3",A1="company4"),B1+5,B1+30))

    the B column of my spreadsheet contains dates. the problem is, if there is
    no date in the B column, this formula returns 1/5/1900. what could i add at
    the end of this formula so that it leaves the cell blank if there is no date
    in column B. thanks in advance.

  2. #2
    Dave F
    Guest

    RE: modifying this formula to return an empty cell

    IF(ISBLANK(B1),"",[then your formula])

    Dave
    --
    Brevity is the soul of wit.


    "Outbacker" wrote:

    > the formula is
    > =IF(OR(A1="company1",A1="company2"),B1+3,IF(OR(A1="company3",A1="company4"),B1+5,B1+30))
    >
    > the B column of my spreadsheet contains dates. the problem is, if there is
    > no date in the B column, this formula returns 1/5/1900. what could i add at
    > the end of this formula so that it leaves the cell blank if there is no date
    > in column B. thanks in advance.


  3. #3
    Nav
    Guest

    RE: modifying this formula to return an empty cell

    Try:

    =IF(OR(A1="company1",A1="company2"),B1+3,IF(OR(A1="company3",A1="company4"),B1+5,IF(B1="","",B1+30)))

    All this does is test a1, if these fail then looks at b1, if there is no
    vlaue then it is blank too, otherwise it adds the 30 onto it.

    Hope it works.



    "Outbacker" wrote:

    > the formula is
    > =IF(OR(A1="company1",A1="company2"),B1+3,IF(OR(A1="company3",A1="company4"),B1+5,B1+30))
    >
    > the B column of my spreadsheet contains dates. the problem is, if there is
    > no date in the B column, this formula returns 1/5/1900. what could i add at
    > the end of this formula so that it leaves the cell blank if there is no date
    > in column B. thanks in advance.


  4. #4
    Outbacker
    Guest

    RE: modifying this formula to return an empty cell

    Hi Dav-
    Your original formula this morning worked great. On this one, you referenced
    A1. That is the company name column that the earlier formula addressed. The
    issue is that the B column (date that the 3,5 and 30 is added to) sometimes
    is blank. If so, the formula returns 1/5/1900. If theres no date in the B
    column, I was hoping for the formula to leave the results column blank.
    Thats the column thats showing the B1+3,5 and 30 results from your formula.
    Is there a way to add this final criteria to your original formula? Thanks
    again.

    "Outbacker" wrote:

    > the formula is
    > =IF(OR(A1="company1",A1="company2"),B1+3,IF(OR(A1="company3",A1="company4"),B1+5,B1+30))
    >
    > the B column of my spreadsheet contains dates. the problem is, if there is
    > no date in the B column, this formula returns 1/5/1900. what could i add at
    > the end of this formula so that it leaves the cell blank if there is no date
    > in column B. thanks in advance.


+ Reply to Thread

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