Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Math Formula Giving Error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Math Formula Giving Error
=SUM(IF(ISNUMBER(C672:C678-D671:D677),C672:C678-D671:D677))+IF(ISNUMBER(D678-C678),D678-C678)
Entered with Ctrl+Shift+Enter rather than just enter (since this is an array formula) should work. -- Regards, Tom Ogilvy "Rob" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Math Formula Giving Error
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" wrote: =SUM(IF(ISNUMBER(C672:C678-D671:D677),C672:C678-D671:D677))+IF(ISNUMBER(D678-C678),D678-C678) Entered with Ctrl+Shift+Enter rather than just enter (since this is an array formula) should work. -- Regards, Tom Ogilvy "Rob" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Math Formula Giving Error
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-D674:D680),C675:C681-D674:D680))+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. -- Regards, Tom Ogilvy "Rob" wrote in message ... 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" wrote: =SUM(IF(ISNUMBER(C672:C678-D671:D677),C672:C678-D671:D677))+IF(ISNUMBER(D678-C678),D678-C678) Entered with Ctrl+Shift+Enter rather than just enter (since this is an array formula) should work. -- Regards, Tom Ogilvy "Rob" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any idea why this is giving me a #Value! Error? | Excel Worksheet Functions | |||
Giving me a error ( =REF! ) | Excel Worksheet Functions | |||
Function giving error | Excel Discussion (Misc queries) | |||
Function giving Error | Excel Worksheet Functions | |||
UpdateLink giving error 1004 | Excel Programming |