Match Function Error?

[mitalia81]

The match function is returning the wrong value. This is an amortization schedule I made and for some reason the match function only returns the wrong value for a 20 year loan term. If I change the year at the top of my spreadsheet to 21 years or 30 years, for example, match will return the correct value.

In the picture, the payoff value should be 240, not 241.

Is there something I'm doing wrong?

 

[mitalia81]

in case pic doesn't show, here it is again

[/URL] photo hosting[/IMG]

 

[XOR LX]

Hi.

I assume that you have the Show a zero in cells that have zero value option (File/Options/Advanced) unchecked, such that any zeroes in your workbooks are not displayed within their cells?

If so, it could be difficult for you to distinguish between cells which are genuinely empty and cells which contain a zero though which, with this setting applied, also appear empty.

The bottom line is that, since you are using a lookup_value of 0 within your MATCH function, based on your result I would surmise that cell F250 does not actually contain a zero at all, though cell F251 does.

Regards

 

[mitalia81]

Hi.

I assume that you have the Show a zero in cells that have zero value option (File/Options/Advanced) unchecked, such that any zeroes in your workbooks are not displayed within their cells?

If so, it could be difficult for you to distinguish between cells which are genuinely empty and cells which contain a zero though which, with this setting applied, also appear empty.

The bottom line is that, since you are using a lookup_value of 0 within your MATCH function, based on your result I would surmise that cell F250 does not actually contain a zero at all, though cell F251 does.

Regards

Thank you for your Reply. I actually had done conditional formatting AND had the zero value option unchecked, but last night I found out that the value in that cell was actually a decimal going back about 12 decimal places!! You're right, the number wasn't an absolute zero. I fixed the issue with the round function in that column going back 6 decimal places so as not to affect loan payments over time.

Thanks again!

 

[XOR LX]

Ok. Glad you sorted it!

And you're welcome!

Cheers