Math Formula Giving Error

[Guest]

I have this formula that subtracts one cell from another and then adds that
to a running total.

=(C672-D671)+(C673-D672)+(C674-D673)+(C675-D674)+(C676-D675)+(C677-D676)+(C678-D677)+(D678-C678)


My problem is this; on occasion there will be the need to have some cell
pairs blank or they'll contain "N/A" in them. When this happens I get a Value
error. How can I correct this?


Thanks in Advance,
Rob

 

[Tom Ogilvy]

=SUM(IF(ISNUMBER(C672:C678-D671677),C672:C678-D671677))+IF(ISNUMBER(D678-C678),D678-C678)


Entered with Ctrl+Shift+Enter rather than just enter (since this is an array
formula)

should work.

 

[Guest]

It works but as you can tell I have to paste it into nearly a hundred cells
and when I try it doesn't do that auto formula format that excel does. Is
there a way I can make it copy/paste friendly or do I have to edit all the
cells directly?

Thanks So Much,
Rob

 

[Tom Ogilvy]

It shouldn't have a problem.

I copied and pasted it into the next 3 cells in the column and the 3rd cell
(4th counting the original) had this formula:

=SUM(IF(ISNUMBER(C675:C681-D674680),C675:C681-D674680))+IF(ISNUMBER(D681-C681),D681-C681)
and it was showing as array entered, so it calculated correctly - or least
as expected.

If that is what you are talking about.