IF(formula populated currency formatted cell is greater than zero) always returns true

[axiopisty]

I have a calc sheet that maintains a loan amortization/payment history. Each time I make a payment I enter the payment amount and the calc sheet shows me the payment breakdown into principle, interest, escrow, etc… All cells are Currency formatted. The column definitions are as follows:

D: Escrow Amount

F: Principle Balance

G: Payment Amount

H: Interest Amount

Cell F17 is populated with this formula: =F16+H17-G17+D17. This just means, the current Principle Balance equals the previous principle balance plus the current interest charge plus the current escrow charge minus the current payment amount.

It is likely that the escrow amount in D will be a fixed amount for a year until it is recalculated based on current tax rates. So rather than typing the escrow amount in each row (for each payment) I would like to convert D into a formula which basically says, if there was any balance left on the loan after the previous payment, then just copy the previous escrow amount into this months payment. This way, I only have to enter an escrow amount each time that value changes, once a year. The rest of the time the sheet auto populates it with the previous value. But, once the loan is paid in full, and there is no current balance to be paid, then the escrow amount should show 0 (zero) because there is no payment to be made. To accomplish this I am using this very simple formula:

D18: =IF(F17 > 0,D17,0)

D19: =IF(F18 > 0,D18,0)

…

D[n]: =IF(F[n-1] > 0,D[n-1],0)

This just means the current escrow amount is the same as the previous escrow amount if there is any principle remaining to be paid. If there is no principle left to be paid then the escrow amount is 0 (zero).

At least, this is what I think it SHOULD mean. But the formula is not working, meaning the boolean expression checking if the principle remaining is greater than zero always returns true, even when there is no principle balance remaining.

For example:

D314=$188.56 F314=$520.96

D315=$188.56 F315=$0.00

D316=$188.56 F316=$0.00

I expect D316=$0.00, but actually it is $188.56

I don’t understand what I am doing wrong. I know my logic is correct, but there is probably a problem with data types in the formula. Is there any reason an IF statement would always return true for a formula populated, Currency formatted, cell when checking if the value is greater than 0 (zero)?

What do I need to change to get this to work?

[Lupp]

Would you, please, reconsider the subject chosen for your question. As I see it, it doesn’t tell anything understandable about the question.
What about “Unexpected results of comparisons with numeric values formatted a ‘Currency’”?

[axiopisty]

Well, I figured it out. Everything was right, but when calc displays $0.00 for a currency cell, don’t believe it. I don’t know why calc does not truncate $0.00 currency formatted cells to actually be0. I consider this a bug in calc.

The answer is that calc must have been experiencing some rounding issues do to double data types, and the backing value for the cells must have been something like 0.000000000001 or something. To fix this I changed the formula in the F column to: F[n]=ROUND(F[n-1]+H[n]-G[n]+D[n], 2). This fixed the problem so that now D[n]=IF(F[n-1] > 0, D[n-1], 0) works as expected.

[Lupp]

If you want to get amounts rounded you need to round them. There are lots of ways to do so. Formatting does not round and should never do so. However, there is an option Precision as shown I would definitely dissuade you from. There are complications and probably surprises. Be explicit.

[axiopisty]

All this is well understood. But from an end user’s perspective, it is extremely difficult to understand why a formula is not working when there are invisible rounding errors. If the cell shows “$0.00” then why would an end user expect it to be something other than what it says? Luckily, I’m a software engineer and understand that rounding errors would likely be the problem for the situation I described in this post. If I wasn’t a software engineer, I wouldn’t have had any clue as to how to fix this problem.

The point is that if a cell is “formatted” to be a currency, then the value in the cell should match the value displayed. I consider it a bug that this is not the case. The value displayed $0.00 did not match the true value in the cell. I don’t know what that value is/was because as an end user I cannot see it. In my opinion this is a problem (a bug if you will) with calc. If a cell is “currency” with 2 decimals, how could there be rounding problems with 2 decimals?

[Lupp]

Programming end users (as spreadsheet users are) need to know how their tools are working. If someone doesn’t, and I get notice of the fact, I sometimes can help him to understand and to find a solution for his problems. I cannot change the fact that appearance and the reality behind often differ. That’s the case in many everyday matters, and it’s the case in spreadsheet cells next to always. Concerning spreadsheets it’s the result of technical reasons and the conflicts between end users expectations which are very different depending on context and situation. This even if we are speaking of just one end user.
E.g.: If you have two cells A1, A2 both containing the formula =2/3, and a third cell containing the formula =(A1=A2), would you accept a FALSE because A1 and A2 got applied different ‘Numbers’ formats? If so, would you expect everybody to agree?