PDA

View Full Version : [SOLVED] Subtraction formula help



Slap_Shot
04-06-2005, 04:18 PM
Since there's no forum for complete Excel idiots, I'm posting this here. :doh:

I've got a formula that needs to take the first cell, then subtract the 3 cells below it (all these cells, by the way, are called from other workbooks). Here's what I'm using:


=IF(SUM(B4,-B5,-B6,-B7)=0,"",SUM(B4,-B5,-B6,-B7))

Now the trick is that sometimes one of the b5-b7 cells is empty. I thought setting up the formula this way was is supposed to stop the rotten, evil little #VALUE! thing (:devil:) , but its not working. The formula works when there's values in all the cells, but shows the rotten, evil #VALUE! when one cell is empty.

Is there a way for the formula to still perform the calculations even if there's one cell that doesn't have a value?

Thanks in advance!!!

Kieran
04-06-2005, 04:30 PM
try
=b4-b5-b6-b7

If you want to suppress the display of zero's use the cell number format options.

mdmackillop
04-06-2005, 04:38 PM
Hi,
Welcome to VBAX

You could set your source cells to zero if there is an error coming from your external workbook, then a simple formula can be used on the cells.
e.g.
Cell B5
=IF(ISERR([Source.xls]Sheet1!$C$7),0,[Source.xls]Sheet1!$C$7)

I don't believe you can check for errors in your addition formula, other than to return 0 if any of the cells contain an error value.

Anne Troy
04-06-2005, 06:27 PM
Hey, Slap_Shot. I know you!! Welcome to VBAX!

Slap_Shot
04-06-2005, 07:07 PM
You're right, Dreamboat - u do know me. And see what you've done, I'm hooked on Excel and trying to automate everything I can think of!!!

Thanks a million to everyone above for the help, I got it working just great now. :thumb

As an aside, here's how I did it:

For the worksheets that I was calling the data from, I actually modified the error handling formula to


=IF(SUM(B4:B7)=0,"0",SUM(B4:B7))


That way, when it adds up the cells and finds them empty, instead of just leaving the cell blank, it puts in a 0 - which then prevents the formula on the other worksheet from returning an error! (see how much you taught me, Dreamboat!!)

So anyway, thanks again for the help.