Summing two fields.

I am using the following formula to sum two fields. ='First Details'!D178+'Second Details'!D178 This is summing two mortgage payments (first and second mortgage).

To the extent it's important, the formulas from 'First Details' D178 and 'Second Details' D178 are:

'First Details' D178==IF(C177="","",IF(+C177-E177>0,+$C$7,""))
'Second Details' D178==IF(C177="","",IF(+C177-E177>0,+C177-E177,""))

Everything works fine, until the second mortgage is paid off (around year 14) and there is zero payment due (loan is paid off). I get a #VALUE! error.

Any help for this newbie greatly appreciated! To put my newbiness in context, this is really just a modification of a downloaded Excel template. I didn't write the formulas on 'First Details' or 'Second Details' myself.

Thanks!

I believe it is because one of your cells is no longer a number but 'blank' (i.e. the ""). There are a few ways to solve this, but probably the easiest is to change the "" at the end of each of those formulae to 0.

Pauley

I did try replacing all of the instances of "" with 0, and that did not resolve the issue. Do you any any other suggestions?

Best,

Kyle

If push comes to shove, you should upload the spreadsheet and describe how to see passing results vs failing results.

If, however, you want to 'learn how to fish' then you need to trace through the formula to see where the error is originating. The first step is to find the 'root' of the error. For example, does it only show up when you try to add the two fields? Or, do you also see errors in either or both of the D178 cells? If so, then track it down further to the cells used in D178 (i.e. C177 and E177). Keep following the cells used in each formula until you get to the cell that shows an error, but none of the cells used in its formula have an error.

Let's assume 'First Details' D178 shows an error, but C177 and E177 do not. The next step is to use 'Evaluate Formula' inthe Formula ribbon for cell D178. This will hopefully show why the error is happening. It is not always obvious, but hopefully you can see how the error occurs and then fix it.

Pauley

Happy to upload file. I tried to trace the origin of the error, but that only made me dizzy and want a glass of bourbon.

I hope the basic construct is self-explanatory, but I created a hyperlink on the Summary tab that takes you directly to the cells that return an error.

Many thanks!

The person who created the sheet chose to show blank cells instead of 0's. You cannot add a blank ("") to a number. The problem starts at cell D171 in your Summary tab (I have a feeling you created this sheet). That cell is trying to add cell D180 in your 'Second Details' sheet, which is not a number, to cell D180 in your 'First Details' sheet, which is a number.

Ideally, you would just get rid of all the blanks, but this is probably more efficient for you.

In cell Summary!D2:
=VALUE("0"&'First Details'!D11)+VALUE("0"&'Second Details'!D11)
Then drag that down to the end.

Pauley