Leave other cells blank if no data in cell.

[Oryan77]

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!

[shemayisroel]

Hi & Welcome,


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

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

[T. Valko]

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.

Try this instead:

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

Or:

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

[Oryan77]

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?

[T. Valko]

Use the same technique:

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

[Aladin Akyurek]

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.

[Oryan77]

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.

[Aladin Akyurek]

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?

[scottylad2]

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

Spreadsheet Formulas

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

Excel tables to the web >> Excel Jeanie HTML 4

[scottylad2]

Sheet1

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

Spreadsheet Formulas

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

Excel tables to the web >> Excel Jeanie HTML 4