+ Reply to Thread
Results 1 to 13 of 13

if one or both of the columns used to calculate the formula are blank, enter nothing

  1. #1
    Registered User
    Join Date
    04-19-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    7

    if one or both of the columns used to calculate the formula are blank, enter nothing

    I want to calculate the number of days between the date in column F and E and G and E, and report the larger number in column H. I want to have column H stay blank if either column F or G is blank. So far, I can't figure out how to add " if column G is blank" to my formula without getting an error code.

    E F G H

    1 4/15/13 4/25/13 4/30/13 15
    2 4/1/13 4/25/13
    3 4/15/13 4/20/13

    =IF(F1="","", MAX((F1-E1),(G1-E1)))

    Thank you Excel wizards for your help!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    Hi and welcome to the forum

    Try this...
    =IF(or(F1="",G1=""),"", MAX(F1-E1,G1-E1))
    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

  3. #3
    Registered User
    Join Date
    04-19-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    Oh my gosh. You are so awesome. Thank you for responding so fast. I have anther question for you. If I allow n/a to be put in the date column, can I then allow the calculation? by way of explanation, one column is for the patient and the other for the partner and if there is no partner, I want the person entering the data to type in n/a and still have the calculation done. Am I making sense?
    Thanks again for your help. Can't believe how long I have been working on this and you fixed it in a flash for me!
    Last edited by JulesTruly; 04-19-2013 at 11:41 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    Thanks for the kind words

    If I understand you correctly, try this. You may need to swap the ranges around if I have them mixed up...

    =IF(or(F1="",G1=""),"", if(F1=0,G1-E1,MAX(F1-E1,G1-E1))
    or
    =IF(or(F1="",G1=""),"", if(G1=0,F1-E1,MAX(F1-E1,G1-E1))

  5. #5
    Registered User
    Join Date
    04-19-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    Now I am getting a "VALUE! error.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    OK maybe Im missing something. can you upload a sample workbook please?
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  7. #7
    Registered User
    Join Date
    04-19-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    Thank you very much for the detailed instructions. I have attached the file.
    Attached Files Attached Files

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    oops OK my bad, I used "0" instead of n/a...but your files showed me that you actually want the test both ways, so...

    =IF(OR(F2="",G2=""),"",IF(F2="n/a",G2-E2,IF(G2="n/a",F2-E2,MAX(F2-E2,G2-E2))))

    Also, you will need to be careful that only n/a is entered and not na or n.a or n-a etc

  9. #9
    Registered User
    Join Date
    04-19-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    I thought I tried changing that but apparently I didn't do that correctly either.
    Thank you so much for taking the time to help me. I really appreciate it.
    I wish I had found this forum earlier, but I am so happy I know about it now!
    Thanks again.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    Maybe you did the same as I did, when I 1st changed it, I changed it to na instead of n/a?

    Im happy we managed to get your question answered, thanks for the feedback, and thanks for joining the forum

  11. #11
    Registered User
    Join Date
    04-19-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    FDibbins, can I ask you another question? I have a column that I have locked so the user can not enter anything in it and the formula remains hidden. It is the one you helped me with the other day
    When I password protect the sheet, my user can no longer sort it. Is there a way to lock a column, but also allow for sorting? Hope this makes sense!
    Thanks again.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    If you allow them to open it as "read-only", they can do whatever they want to the file, but they cannot save it

  13. #13
    Registered User
    Join Date
    04-19-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: if one or both of the columns used to calculate the formula are blank, enter nothing

    I was afraid that might be the case. Thanks so much for all your help.

+ 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