Mark O'Brien
MrExcel MVP
- Joined
- Feb 15, 2002
- Messages
- 3,530
OK, I have a named range in cells C20:V20 and the name of the Range is "PV". The values are as follows:<pre>
0;$7,109;$13,927;$20,466;$26,738;$32,754;$38,525;$44,060;$49,369;$54,462;$59,348;$64,034;$68,530;$72,843;$76,981;$80,950;$84,759;$88,412;$91,918;;$95,281;$98,508</pre>
Now, my ultimate goal is interpolate between these values which are for years 0 - 20. I intend to use
the result of a MATCH to tell me my years. e.g. MATCH - 1 will give me 0 years if the value I'm looking for is $2,000.
I am using a formula like this to return my values:
=INDEX(PV,1,MATCH(PV,A4,1))
Where:
PV is my named range
A4 houses the value to be interpolated.
I have got my interpolation formula working, so that isn't an issue. What is the issue is that all of my
results using the above formula are 0. i.e. I put this formula in B4 and copied down the column for values $2,000 through to $50,000.
What am I doing wrong with this INDEX/MATCH formula?
It just seems to be giving me the smallest value in the named range when it can't find an exact match.
TIA
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-09-13 10:40
This message was edited by Mark O'Brien on 2002-09-13 10:41
0;$7,109;$13,927;$20,466;$26,738;$32,754;$38,525;$44,060;$49,369;$54,462;$59,348;$64,034;$68,530;$72,843;$76,981;$80,950;$84,759;$88,412;$91,918;;$95,281;$98,508</pre>
Now, my ultimate goal is interpolate between these values which are for years 0 - 20. I intend to use
the result of a MATCH to tell me my years. e.g. MATCH - 1 will give me 0 years if the value I'm looking for is $2,000.
I am using a formula like this to return my values:
=INDEX(PV,1,MATCH(PV,A4,1))
Where:
PV is my named range
A4 houses the value to be interpolated.
I have got my interpolation formula working, so that isn't an issue. What is the issue is that all of my
results using the above formula are 0. i.e. I put this formula in B4 and copied down the column for values $2,000 through to $50,000.
What am I doing wrong with this INDEX/MATCH formula?
It just seems to be giving me the smallest value in the named range when it can't find an exact match.
TIA
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-09-13 10:40
This message was edited by Mark O'Brien on 2002-09-13 10:41