Leave other cells blank if no data in cell.

This is a discussion on Leave other cells blank if no data in cell. within the Excel Questions forums, part of the Question Forums category; Hi, I have 2 similar question. I'm using a formula that I got from a previously made excel sheet. The ...

1. Leave other cells blank if no data in cell.

Hi, I have 2 similar question.

I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this:
=INT((D10-10)/2)

The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.

If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.

I think it has to do with using an IF statement followed with ""? Am I on the right track?

Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?

I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.

Thanks!

2. Re: Leave other cells blank if no data in cell.

Hi & Welcome,

Problem #1, try =IF(D10="","",INT((D10-10)/2))

Problem #2, try =IF(SUM(AP3:AQ6)=0,"",SUM(AP3:AQ6))

3. Re: Leave other cells blank if no data in cell.

Originally Posted by shemayisroel

Problem #2, try =IF(SUM(AP3:AQ6)=0,"",SUM(AP3:AQ6))
If there might be negative numbers involved checking for SUM = 0 might not be a good idea.

=IF(COUNT(AP3:AQ6)=0,"",SUM(AP3:AQ6))

Or:

=IF(COUNT(AP3:AQ6),SUM(AP3:AQ6),"")

4. Re: Leave other cells blank if no data in cell.

Originally Posted by #NAME?
If there might be negative numbers involved checking for SUM = 0 might not be a good idea.
Nope, no negative numbers will ever be entered. So I think shemayisroal's formula is just what I needed. Thanks guys!

I now have an idea and I'm wondering if it's even possible to do with a formula.

I currently have cell K20 with this formula:

=SUM(Y20,AA20,AC20,AE20,AG20)

The thing is, I don't want it to add up those cells unless I have a number entered in cell Y20. So, if I have numbers in the other cells, but not in cell Y20, then I would like K20 to be left blank. But if I enter a number in cell Y20, then I would like K20 to go ahead and add up all of those cells.

Is that possible?

5. Re: Leave other cells blank if no data in cell.

Use the same technique:

=IF(COUNT(Y20),SUM(Y20,AA20,AC20,AE20,AG20),"")

6. Re: Leave other cells blank if no data in cell.

Originally Posted by Oryan77
Nope, no negative numbers will ever be entered. So I think shemayisroal's formula is just what I needed. Thanks guys!

I now have an idea and I'm wondering if it's even possible to do with a formula.

I currently have cell K20 with this formula:

=SUM(Y20,AA20,AC20,AE20,AG20)

The thing is, I don't want it to add up those cells unless I have a number entered in cell Y20. So, if I have numbers in the other cells, but not in cell Y20, then I would like K20 to be left blank. But if I enter a number in cell Y20, then I would like K20 to go ahead and add up all of those cells.

Is that possible?
Try...

=IF(ISNUMBER(Y20),SUM(Y20,AA20,AC20,AE20,AG20),"")

Note that 0 is also a number.

7. Re: Leave other cells blank if no data in cell.

Try...

=IF(ISNUMBER(Y20),SUM(Y20,AA20,AC20,AE20,AG20),"")

Note that 0 is also a number.
Well it seems to still be adding in the number that I have entered in cell AA20. It seems to be acting the same as when I had it figuring up the sum of all of those cells. I see no change.

8. Re: Leave other cells blank if no data in cell.

Originally Posted by Oryan77
Well it seems to still be adding in the number that I have entered in cell AA20. It seems to be acting the same as when I had it figuring up the sum of all of those cells. I see no change.
Enter 1 in Y20.

Enter L20:

=ISNUMBER(Y20)

what result do you get in L20?

9. Re: Leave other cells blank if no data in cell.

Originally Posted by Oryan77
Nope, no negative numbers will ever be entered. So I think shemayisroal's formula is just what I needed. Thanks guys!

I now have an idea and I'm wondering if it's even possible to do with a formula.

I currently have cell K20 with this formula:

=SUM(Y20,AA20,AC20,AE20,AG20)

The thing is, I don't want it to add up those cells unless I have a number entered in cell Y20. So, if I have numbers in the other cells, but not in cell Y20, then I would like K20 to be left blank. But if I enter a number in cell Y20, then I would like K20 to go ahead and add up all of those cells.

Is that possible?
I've used my own cell refs, but this works
Sheet1

 G H I J K L 20 10 15 25 25

 Cell Formula L20 =IF(G20="","",SUM(H20,I20,J20,K20))

Excel tables to the web >> Excel Jeanie HTML 4

10. Re: Leave other cells blank if no data in cell.

Sheet1

 G H I J K L 20 25 10 15 25 25 75

 Cell Formula L20 =IF(G20="","",SUM(H20,I20,J20,K20))

Excel tables to the web >> Excel Jeanie HTML 4

Page 1 of 4 123 ... Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•