Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Result not less than 0
I'm doing mortgage calculations such as
=-CUMIPMT(H13/12,H14*12,H12,1,12,0)and can't figure out how to have a result that, when the result is a negative number, zero is entered by default. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Result not less than 0
Use the MAX function. Assuming your posted formula is the one you want to
use... =MAX(0,-CUMIPMT(H13/12,H14*12,H12,1,12,0)) Rick "lena" wrote in message ... I'm doing mortgage calculations such as =-CUMIPMT(H13/12,H14*12,H12,1,12,0)and can't figure out how to have a result that, when the result is a negative number, zero is entered by default. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Result not less than 0
I still get an answer of #NUM! Let me try again:
1) Original formula works for computing loan interest: =-CUMIPMT(H35/12,H37*12,H34,1,12,0) 2) But when H35 (interest rate) is a value of 0 I get #NUM! -- lena "Rick Rothstein (MVP - VB)" wrote: Use the MAX function. Assuming your posted formula is the one you want to use... =MAX(0,-CUMIPMT(H13/12,H14*12,H12,1,12,0)) Rick "lena" wrote in message ... I'm doing mortgage calculations such as =-CUMIPMT(H13/12,H14*12,H12,1,12,0)and can't figure out how to have a result that, when the result is a negative number, zero is entered by default. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Result not less than 0
I think you are now asking a different question than in your original post.
I am not personally familiar with the CUMIPMT function, but the help file says in the Remarks section that "If rate ‰¤ 0, nper ‰¤ 0, or pv ‰¤ 0, CUMIPMT returns the #NUM! error value." So, it looks like your question is how to get rid of the #NUM! error displayed if the first argument to the function is zero or less. If I have understood this all correctly, then I think this formula will do what you want... =IF(H35<=0,"",-CUMIPMT(H35/12,H37*12,H34,1,12,0)) Where the "" displays an empty string if H35 is equal to or less than zero. You could put your own "error message" in there if you want that instead. For example... =IF(H35<=0,"Oops!",-CUMIPMT(H35/12,H37*12,H34,1,12,0)) Rick "lena" wrote in message ... I still get an answer of #NUM! Let me try again: 1) Original formula works for computing loan interest: =-CUMIPMT(H35/12,H37*12,H34,1,12,0) 2) But when H35 (interest rate) is a value of 0 I get #NUM! -- lena "Rick Rothstein (MVP - VB)" wrote: Use the MAX function. Assuming your posted formula is the one you want to use... =MAX(0,-CUMIPMT(H13/12,H14*12,H12,1,12,0)) Rick "lena" wrote in message ... I'm doing mortgage calculations such as =-CUMIPMT(H13/12,H14*12,H12,1,12,0)and can't figure out how to have a result that, when the result is a negative number, zero is entered by default. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
vlookup based on random result returns incorrect result | Excel Worksheet Functions |