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

1. ## 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. ## 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))

3. ## 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!

4. ## 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. ## 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. ## 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. ## 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.

8. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

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

#### 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