Help with formula trowing a #value! result

[axelman]

Hi

I've downloaded a mortgage calculator from the web, being anewbie I'm stuck
with the following issue:

When the last payment comes down to 0 or near 0 like 0.03 cents all the
formulas values return a #VALUE!, I want that the value be displayed as 0.

In example the maximum payments are 431 monthly payments, lets say the last
payment the client made was for 378, now after that paymet I need the value
to be displayed as 0 up to row 431, because he has finished his payments.

A formula is

if(F378+D378<C378,C378-(D378+F378),"")

EVALUATES TO------> if(""+0<C378,C378-(D378+F378),"")

THAT RETURNS-----> #VALUE!

Can you help me, it would be greately appreciated

 

[David Biddulph]

You are trying to add a text string to a number, and that gives the #VALUE!
error.

If you want it to show zero, instead of blank, change
if(F378+D378<C378,C378-(D378+F378),"") to
if(F378+D378<C378,C378-(D378+F378),0) or to
MAX(C378-(D378+F378),0)

 

[Joe User]

A formula is
if(F378+D378<C378,C378-(D378+F378),"")
EVALUATES TO------> if(""+0<C378,C378-(D378+F378),"")
THAT RETURNS-----> #VALUE!

Seems odd to see that in a template. What's the URL from which you are
downloading the mortgage calculator? Or if you are using Excel to find it,
exactly what search string are you specifying and what title are you
selecting?

Anyway, I would suggest:

=IF(COUNT(F378,D378,C378)<>3, "",
IF(F378+D378<C378,C378-(D378+F378),""))

Of course, you might want to propagate that up and down the column.

Furthermore, I might be tempted to write the following:

=IF(COUNT(F378,D378,C378)<>3, "", MAX(0, C378-D378-F378))


----- original message -----

 

[axelman]

Thanks for your help but still have same issues

please download my current worksheet from
http://www.iodatamicro.com/worksheet/loan.xlsx

can you please take a look at the formulas and send me your suggestions

 

[axelman]

Hi

Here is the link again

please download my current worksheet from
http://www.iodatamicro.com/worksheet/

Just click on the download link

Can you please take a look at the formulas and send me your suggestions.

 

[Joe User]

please download my current worksheet from
http://www.iodatamicro.com/worksheet/ [....]
Can you please take a look at the formulas
and send me your suggestions.

Much too complex a zip file for me take risk downloading to my computer.

Can you isolate the problem in a simple worksheet?

What happened when you used the IF(COUNT...) solution that I suggested?


----- original message -----

 

[axelman]

Yes I already did, this link lis in my webserver, I do not know why
internet explorer downloads as a zip file.

I use fire fox and -->right mouse click on the download link--> then save
link as, you should be able to download just the loan.xlsx file it is just
57 kb

Thanks in advanced for your help

please download my current worksheet from
http://www.iodatamicro.com/worksheet/ [....]
Can you please take a look at the formulas
and send me your suggestions.

Much too complex a zip file for me take risk downloading to my computer.

Can you isolate the problem in a simple worksheet?

What happened when you used the IF(COUNT...) solution that I suggested?


----- original message -----

Hi

Here is the link again

please download my current worksheet from
http://www.iodatamicro.com/worksheet/

Just click on the download link

Can you please take a look at the formulas and send me your suggestions.

 

[David Biddulph]

Let us go through this slowly:
1 Joe asked you for the URL from which you got this mortgage calculator.
You haven't answered that.
2 Joe suggested two amended formulae including IF(COUNT(...) and asked
what happened with that formula. You haven't answered that.
3 I gave you two suggestions, either
if(F378+D378<C378,C378-(D378+F378),0) or MAX(C378-(D378+F378),0). You
haven't answered those suggestions.
4 If you are having problems with the suggestions that Joe and I have
given you, you haven't told us what formulae or values are in the cells
C378, D378, and F378.
5 You've been told that a #VALUE! error comes from trying to add a text
string such as "" to a number. Have you looked to see whether you are still
trying to do that?
6 Do you expect other users to try to help you, or are you going to
respond to the questions and suggestions you've already had?
--
David Biddulph

Yes I already did, this link lis in my webserver, I do not know why
internet explorer downloads as a zip file.

I use fire fox and -->right mouse click on the download link--> then save
link as, you should be able to download just the loan.xlsx file it is just
57 kb

Thanks in advanced for your help

please download my current worksheet from
http://www.iodatamicro.com/worksheet/ [....]
Can you please take a look at the formulas
and send me your suggestions.

Much too complex a zip file for me take risk downloading to my computer.

Can you isolate the problem in a simple worksheet?

What happened when you used the IF(COUNT...) solution that I suggested?


----- original message -----

Hi

Here is the link again

please download my current worksheet from
http://www.iodatamicro.com/worksheet/

Just click on the download link

Can you please take a look at the formulas and send me your suggestions.


Hi

I've downloaded a mortgage calculator from the web, being anewbie I'm
stuck with the following issue:

When the last payment comes down to 0 or near 0 like 0.03 cents all the
formulas values return a #VALUE!, I want that the value be displayed
as 0.

In example the maximum payments are 431 monthly payments, lets say the
last payment the client made was for 378, now after that paymet I need
the value to be displayed as 0 up to row 431, because he has finished
his payments.

A formula is

if(F378+D378<C378,C378-(D378+F378),"")

EVALUATES TO------> if(""+0<C378,C378-(D378+F378),"")

THAT RETURNS-----> #VALUE!

Can you help me, it would be greately appreciated

 

[Joe User]

I do not know why internet explorer downloads as a zip file.
I use fire fox [....] you should be able to download just
the loan.xlsx file

Yeah, I had wanted to use Firefox. But coincidentally, I had updated to the
latest revision today, and there is something wrong it (on my computer).

I had tried to use your original URL, which ended with loan.xlsx. But that,
too, downloaded a zip file with many files.

Go figure! I don't know enough about browser technology to speculate what
the problem might be.


----- original message -----

Yes I already did, this link lis in my webserver, I do not know why
internet explorer downloads as a zip file.

I use fire fox and -->right mouse click on the download link--> then save
link as, you should be able to download just the loan.xlsx file it is just
57 kb

Thanks in advanced for your help

please download my current worksheet from
http://www.iodatamicro.com/worksheet/ [....]
Can you please take a look at the formulas
and send me your suggestions.

Much too complex a zip file for me take risk downloading to my computer.

Can you isolate the problem in a simple worksheet?

What happened when you used the IF(COUNT...) solution that I suggested?


----- original message -----

Hi

Here is the link again

please download my current worksheet from
http://www.iodatamicro.com/worksheet/

Just click on the download link

Can you please take a look at the formulas and send me your suggestions.


Hi

I've downloaded a mortgage calculator from the web, being anewbie I'm
stuck with the following issue:

When the last payment comes down to 0 or near 0 like 0.03 cents all the
formulas values return a #VALUE!, I want that the value be displayed
as 0.

In example the maximum payments are 431 monthly payments, lets say the
last payment the client made was for 378, now after that paymet I need
the value to be displayed as 0 up to row 431, because he has finished
his payments.

A formula is

if(F378+D378<C378,C378-(D378+F378),"")

EVALUATES TO------> if(""+0<C378,C378-(D378+F378),"")

THAT RETURNS-----> #VALUE!

Can you help me, it would be greately appreciated

 

[Joe User]

I use fire fox [....] you should be able to download
just the loan.xlsx file

I finally fixed Firefox on my computer, and I was able to download
loan.xlsx.

Thanks for your help but still have same issues

The change I suggested elsewhere does indeed fix the problem that you
reported. The fix is:

=IF(COUNT(F378,D378,C378)<>3, "", MAX(0, C378-D378-F378))

copied up and down the column.

But as you say, the template seems to have many other problems. I do not
wish to try to fix every design flaw in the template. That is someone
else's job.


----- original message -----

I do not know why internet explorer downloads as a zip file.
I use fire fox [....] you should be able to download just
the loan.xlsx file

Yeah, I had wanted to use Firefox. But coincidentally, I had updated to
the latest revision today, and there is something wrong it (on my
computer).

I had tried to use your original URL, which ended with loan.xlsx. But
that, too, downloaded a zip file with many files.

Go figure! I don't know enough about browser technology to speculate what
the problem might be.


----- original message -----

Yes I already did, this link lis in my webserver, I do not know why
internet explorer downloads as a zip file.

I use fire fox and -->right mouse click on the download link--> then
save link as, you should be able to download just the loan.xlsx file it
is just 57 kb

Thanks in advanced for your help

please download my current worksheet from
http://www.iodatamicro.com/worksheet/
[....]
Can you please take a look at the formulas
and send me your suggestions.

Much too complex a zip file for me take risk downloading to my computer.

Can you isolate the problem in a simple worksheet?

What happened when you used the IF(COUNT...) solution that I suggested?


----- original message -----

Hi

Here is the link again

please download my current worksheet from
http://www.iodatamicro.com/worksheet/

Just click on the download link

Can you please take a look at the formulas and send me your
suggestions.


Hi

I've downloaded a mortgage calculator from the web, being anewbie I'm
stuck with the following issue:

When the last payment comes down to 0 or near 0 like 0.03 cents all
the formulas values return a #VALUE!, I want that the value be
displayed as 0.

In example the maximum payments are 431 monthly payments, lets say the
last payment the client made was for 378, now after that paymet I need
the value to be displayed as 0 up to row 431, because he has finished
his payments.

A formula is

if(F378+D378<C378,C378-(D378+F378),"")

EVALUATES TO------> if(""+0<C378,C378-(D378+F378),"")

THAT RETURNS-----> #VALUE!

Can you help me, it would be greately appreciated

 

[axelman]

OK, thank you very much for your help I was able to fix every formula using
your advice, pull an all nighter but worth the time, you guys rcok!!

I use fire fox [....] you should be able to download
just the loan.xlsx file

I finally fixed Firefox on my computer, and I was able to download
loan.xlsx.

Thanks for your help but still have same issues

The change I suggested elsewhere does indeed fix the problem that you
reported. The fix is:

=IF(COUNT(F378,D378,C378)<>3, "", MAX(0, C378-D378-F378))

copied up and down the column.

But as you say, the template seems to have many other problems. I do not
wish to try to fix every design flaw in the template. That is someone
else's job.


----- original message -----

I do not know why internet explorer downloads as a zip file.
I use fire fox [....] you should be able to download just
the loan.xlsx file

Yeah, I had wanted to use Firefox. But coincidentally, I had updated to
the latest revision today, and there is something wrong it (on my
computer).

I had tried to use your original URL, which ended with loan.xlsx. But
that, too, downloaded a zip file with many files.

Go figure! I don't know enough about browser technology to speculate
what the problem might be.


----- original message -----

Yes I already did, this link lis in my webserver, I do not know why
internet explorer downloads as a zip file.

I use fire fox and -->right mouse click on the download link--> then
save link as, you should be able to download just the loan.xlsx file it
is just 57 kb

Thanks in advanced for your help
"Joe User" <joeu2004> wrote in message
please download my current worksheet from
http://www.iodatamicro.com/worksheet/
[....]
Can you please take a look at the formulas
and send me your suggestions.

Much too complex a zip file for me take risk downloading to my
computer.

Can you isolate the problem in a simple worksheet?

What happened when you used the IF(COUNT...) solution that I suggested?


----- original message -----

Hi

Here is the link again

please download my current worksheet from
http://www.iodatamicro.com/worksheet/

Just click on the download link

Can you please take a look at the formulas and send me your
suggestions.


Hi

I've downloaded a mortgage calculator from the web, being anewbie
I'm stuck with the following issue:

When the last payment comes down to 0 or near 0 like 0.03 cents all
the formulas values return a #VALUE!, I want that the value be
displayed as 0.

In example the maximum payments are 431 monthly payments, lets say
the last payment the client made was for 378, now after that paymet I
need the value to be displayed as 0 up to row 431, because he has
finished his payments.

A formula is

if(F378+D378<C378,C378-(D378+F378),"")

EVALUATES TO------> if(""+0<C378,C378-(D378+F378),"")

THAT RETURNS-----> #VALUE!

Can you help me, it would be greately appreciated