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!

 

[Aladin Akyurek]

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

Looks like...

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

 

[daffy]

Been reading this thread with interest,

Scottylad2

Are you saying that when a cell has a formula in (as this thread has in Y20) it you would need to use =0 instead of ""
Don

 

[scottylad2]

Double quotes didn't work but the 0 did

 

[scottylad2]

Aladins formula does the same, as in lots of cases in Excel there's more than one way to skin a rabbit. There was no possibility of a negative number as was mentioned earlier so 0 was ok to go with

 

[daffy]

so your saying use 0 instead of "" in the formula due to the cell having a formula in it and set options to not show 0's

Would this apply in all cases or just this one?

Thanks for the info

Don

 

[scottylad2]

Excel Workbook
	C	D
1	1	10
2	2	10
3	5	10
4	4	10
5	5	10
6	50
7
8	67
9	100
Sheet1

It only worked because there were no 0's showing. Above you'll see both work ok but below because i'm displaying 0's then it would always be showing the zero, so would still be calculating as a result of that being there.

Excel Workbook
	C	D
1	1
2	2
3	5
4	4
5	5
6	0
7
8
9	0
Sheet1

 

[wsjackman]

to daffy

on this one the zero was neededl
it is definately not the every case solution.

any complex (or simple) equation can give a wrong answer if an input is other than expected. Blank cells are often an issue, and have often given, and still give, problems. the reason it is blank can be because it is empty, is "" as the result of an equation, the contents hidden, such as not displaying zero or being formatted with ";;;".
Typically each one of these needs to be handled differently in referring formulas.

 

[scottylad2]

Because Y20 was looking at the result of a sum, even though there was no numbers in the cells ref'd for the sum, there was a 0 in the box it was just set not to display. a Format trick if you like, but it was still there, so saying if it =0 worked in this case

 

[Oryan77]

Wow, these formulas worked great. Thanks for looking into this for me, I appreciate it. It's actually really cool when a spreadsheet does stuff like this It's perfect.

 

[bksush]

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.

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 without if function?