Need help with #VALUE! error

[lc130]

Hi All

I posted this earlier but now it's too far down the forum to get answered. Attached is a sheet I downloaded to which I added columns starting with K. I believe that something is preventing the values in columns D, E, and I from being read as numbers once the loan is repaid. From that point forward VALUE errors are returned.

Can it be fixed?

Thank you

Charlie

[MrShorty]

It depends on exactly what you are trying to do. My observations:
1) The formula in K168 is different from the rest of the column. It is referring to row 330, and row D330 and E330 are empty string "". Excel does not know how to add (using the + operator) numbers and text. Once the #VALUE error is generated, formulas that refer to this cell will propagate the error.
2) D and E from row 167 on return empty string "". As noted above, Excel cannot add a string to a number, so that contributes to the VALUE error.

Since I'm not sure exactly how you are intending to perform this calculation, I cannot make any solid suggestions. I would check K168 and make sure it is doing what you want. You may want to replace D and E with something that will return a number after the loan is repaid. Or maybe use a SUM() function instead of +/- operators (the SUM() function knows how to ignore text strings) ...SUM(K168,-D168,-E168). I could "fix" the error (as in the spreadsheet would calculate numbers in column K) by getting rid of the empty strings and replacing them with 0s, but I could not otherwise determine if the results were correct.

[lc130]

Or maybe use a SUM() function instead of +/- operators (the SUM() function knows how to ignore text strings) ...SUM(K168,-D168,-E168).

That did it! Many thanks!