Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation
Here's my problem. I have a spreadsheet. There are some Named Ranges.
They are LoanAmount, PurchasePrice, AppraisedValue, and LTV. I have a button on the sheet that calculates the LTV. The code says: Dim LoanAmount As Single, PurchasePrice As Single, etc LoanAmount = Range("LoanAmount").Value etc Range("LTV").Value = LoanAmount / AppraisedValue I have a cell with the formula =IF(LTV<"",IF(LTV80%,"PMI Needed","PMI NOT needed")) However, when LoanAmount = 80,000 and AppraisedValue = 100,000 the value that appears in Range LTV is 80.000% (as formatted) but I see "PMI Needed" because the real value of the calculation is 80.0000011920928% which appears in the formula bar. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation
Try rounding your calculation to a set number of dec places, either as you
calculate, or the final result, or even in the worksheet formula -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "John Pierce" wrote in message om... Here's my problem. I have a spreadsheet. There are some Named Ranges. They are LoanAmount, PurchasePrice, AppraisedValue, and LTV. I have a button on the sheet that calculates the LTV. The code says: Dim LoanAmount As Single, PurchasePrice As Single, etc LoanAmount = Range("LoanAmount").Value etc Range("LTV").Value = LoanAmount / AppraisedValue I have a cell with the formula =IF(LTV<"",IF(LTV80%,"PMI Needed","PMI NOT needed")) However, when LoanAmount = 80,000 and AppraisedValue = 100,000 the value that appears in Range LTV is 80.000% (as formatted) but I see "PMI Needed" because the real value of the calculation is 80.0000011920928% which appears in the formula bar. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation
Try using double instead of single.
But floating point arithmetic (which is what is being used) always has the difficulty that decimal numbers do not translate exactly so you get precision problems: I would recommend using a tolerance for your 80% ie use 80.0001 Charles ______________________ Decision Models FastExcel Version 2 now available. www.DecisionModels.com/FxlV2WhatsNew.htm "John Pierce" wrote in message om... Here's my problem. I have a spreadsheet. There are some Named Ranges. They are LoanAmount, PurchasePrice, AppraisedValue, and LTV. I have a button on the sheet that calculates the LTV. The code says: Dim LoanAmount As Single, PurchasePrice As Single, etc LoanAmount = Range("LoanAmount").Value etc Range("LTV").Value = LoanAmount / AppraisedValue I have a cell with the formula =IF(LTV<"",IF(LTV80%,"PMI Needed","PMI NOT needed")) However, when LoanAmount = 80,000 and AppraisedValue = 100,000 the value that appears in Range LTV is 80.000% (as formatted) but I see "PMI Needed" because the real value of the calculation is 80.0000011920928% which appears in the formula bar. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi threaded calculation (multi CPU) - impact on calculation spe | Excel Discussion (Misc queries) | |||
age calculation | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
range.calculation with UDF not working when calculation is set to automatic | Excel Programming |