Leave other cells blank if no data in cell.

Oryan77

Board Regular
Joined
May 9, 2009
Messages
176
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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi & Welcome,


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

Problem #2, try =IF(SUM(AP3:AQ6)=0,"",SUM(AP3:AQ6))
 
Upvote 0
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),"")
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
Excel Workbook
GHIJKL
2010152525 
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top