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!

 

[scottylad2]

Excel Workbook
	G	H	I	J	K	L
20	25	10	15	25	25	100
Sheet1

I hadn't noticed you also want to include the criteria cell in your sum, so heres what you want i think. Essentially what your saying, is if G20 is blnk, leave blank, otherwise (which means if there's a number in G20, sum all the cells mentioned in the SUM.

hope this makes sense

 

[Oryan77]

Enter 1 in Y20.

Enter L20:

=ISNUMBER(Y20)

what result do you get in L20?

Do you mean enter K20? That is the cell I'm trying to add the formula to. When I change the formula in K20 to =ISNUMBER(Y20) I get TRUE.

I didn't think this would matter, but I will mention it anyway in case I am wrong:

The cell that has data in it is cell AA20. Cell AA20 has the formula =($F$10). Cell F10 has a 1 entered. So cell AA20 has a 1 and that is the data that shows up in cell K20 even when I use your formula.

But with your formula, when I leave cell Y20 blank, and keep the 1 in cell F10, the 1 still shows up in cell K20.

To make things even more complicated, I do have a formula in Y20, =$AW$7. But there is no data in AW7.

Your formula reads all the cells, K20 just doesn't seem to ignore the data in AA20 when I have Y20 blank.

I hadn't noticed you also want to include the criteria cell in your sum, so heres what you want i think. Essentially what your saying, is if G20 is blnk, leave blank, otherwise (which means if there's a number in G20, sum all the cells mentioned in the SUM.

Your formula still seems to do the same thing (K20 doesn't ignore the data in AA20 when cell Y20 is blank). It's possible that my original question wasn't clear and you guys are helping me do something different. So just in case, I'll explain it again:

Cell K20 needs to add up these cells (Y20,AA20,AC20,AE20,AG20). But it should only add them up if there is data in cell Y20. K20 should stay blank if there is data in any other cell if cell Y20 is blank. But if I enter a number in Y20, then K20 adds everything up like normal, including cell Y20.

I hope I'm helping

 

[wsjackman]

what do you get with just
=isnumber(Y20)
when y20 appears to be blank
when you enter 0 in Y20


what happens when you enter 0 in a cell
could you have zeros set to not display?


if w7 is truely empty, =w7 should have a 0 result

 

[Oryan77]

what do you get with just
=isnumber(Y20)
when y20 appears to be blank
when you enter 0 in Y20


what happens when you enter 0 in a cell
could you have zeros set to not display?


if w7 is truely empty, =w7 should have a 0 result

I did have zeros set to not display, so I turned that back on for your test.

When I type =isnumber(Y20) in cell K20, I get TRUE. Y20 shows a 0 and AW7 also shows a 0.

 

[scottylad2]

Excel Workbook
	C	D	E	F	G	H	I	J	K
20	125		125		125		125		500
Sheet2

This is your same set of results. Again i've adjusted the cell refs so i can show it here, i'm not sure why it won't work with you. With data in the criteria cell and below with no data

Excel Workbook
	C	D	E	F	G	H	I	J	K
20			125		125		125
Sheet2

 

[scottylad2]

K20 is only doing the sum if there is an entry in the criteria cell, i've used C20 but it could be any. If i have data in C20 it adds up and displays in K20, nothing in C20 and k20 is blank. Is that not what you're looking for? maybe i'm misunderstanding you

 

[scottylad2]

<table style="border-collapse: collapse; width: 48pt;" width="64" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20"> =IF(Y20="","",SUM(Y20,AA20,AC20,AE20,AC20))</td> </tr></table>
this is your formula, write it into K20

 

[Oryan77]

I still get the same results. I went ahead and stuck a dumbed down version of my xls file in case someone wants to look at it.

http://www.stanford.edu/~rchilton/oryan/formula.zip

I highlighted the cells that reference each other with the same color. I hope this will help.

I'm a beginner as far as formulas are concerned. So I apologize if you find out that I left out important information that would have helped earlier.

Let me know if this is helpful, thanks a lot for your guys patience & assistance!

 

[scottylad2]

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
20																	1
character

Your problem was that there is a formula in the cell, maybe you already mentioned it but the fix is to swap the first set of double quotes wuth 0

 

[scottylad2]

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
18																	Ability		Size				Temp
19	ATK 4														ATK 4		Mod		Mod		_____		Mod
20	2														1		1
character

With something in AW7
and below without anything in there

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
18																	Ability		Size				Temp
19	ATK 4														ATK 4		Mod		Mod		_____		Mod
20																	1
character