#N/A - "Value not available"

[Terry McCoy]

Folks - I am having a problem with a workbook in Excel 2007. I have what
appear to be values, - numbers - in cells, but formulas are returning an #N/A
error that the value is not available. I am getting the error from an
HLOOKUP formula in one spreadsheet, and I was also getting it from another
spreadsheet in the same workbook when I tried to use the average function and
the array function. I used the "isnumber" function, and they are numbers.
The HLOOKUP formula is referring to date in a table. It was working fine
before, but now I am getting this error. This workbook is from another one
that I "Saved As" and then changed the input. The formulas seem to be fine
in the table. I have not been able to figure it out and would appreciate any
insight.

Thanks for your help.

Terry McCoy

 

[Niek Otten]

Hi Terry,

Post your formula, input vales and relevant table values. Is the table sorted?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Folks - I am having a problem with a workbook in Excel 2007. I have what
| appear to be values, - numbers - in cells, but formulas are returning an #N/A
| error that the value is not available. I am getting the error from an
| HLOOKUP formula in one spreadsheet, and I was also getting it from another
| spreadsheet in the same workbook when I tried to use the average function and
| the array function. I used the "isnumber" function, and they are numbers.
| The HLOOKUP formula is referring to date in a table. It was working fine
| before, but now I am getting this error. This workbook is from another one
| that I "Saved As" and then changed the input. The formulas seem to be fine
| in the table. I have not been able to figure it out and would appreciate any
| insight.
|
| Thanks for your help.
|
| Terry McCoy

 

[Terry McCoy]

Niek - Thanks for replying. The formula is:
=HLOOKUP($D44,$J$54:$N$64,F6+1,FALSE)

The input values in the table are: 7.5% for the horizontal value, which is
an investment or capitalization rate, and 5 for the vertical value, which is
5 years. The table cycles the investment rate and time period through an
investment formula. The values in the table seem to be correct, and they
change when I change the input variables in the table. The HLOOKUP formula
just does not seem to recognize them as values. The table is listed below:

7.00% 7.25% 7.50% 7.75% 8.00%
1 $141.04 $136.18 $131.64 $127.39 $123.41
2 $176.95 $170.85 $165.15 $159.82 $154.83
3 $184.56 $178.20 $172.26 $166.70 $161.49
4 $191.55 $184.95 $178.78 $173.02 $167.61
5 $194.41 $187.70 $181.45 $175.59 $170.11
6 $203.93 $196.90 $190.34 $184.20 $178.44
7 $209.93 $202.69 $195.94 $189.61 $183.69
8 $219.27 $211.70 $204.65 $198.05 $191.86
9 $225.80 $218.01 $210.74 $203.94 $197.57
10 $232.86 $224.83 $217.33 $210.32 $203.75

This spreadsheet was originally in Excel 2003 and I converted it to Excel
2007. Could that have anything to do with it? I am having what seems to be
the same problem in another worksheet in this same workbook. I created this
workbook by doing a "Save As" of another Workbook. Since the problem seems
to be in more than just one place, I wonder if it might have to do with the
"Save As"?.

Thank you very much, I would appreciate any help.

Terry McCoy.

 

[vezerid]

Terry,

Try the following:

=MATCH(7.50%,J54:N54,0)
=MATCH(D44,J54:N54,0)

=MATCH(5,J54:J64,0)
=MATCH(F6+1,J54:J64,0)

=INDEX($J$54:$N$64,MATCH($D44,$J$54:$N$54,0),MATCH(F6+1,$J$54:$J
$64,0))

Does this give you any clues?

HTH
Kostis Vezerides

 

[Terry McCoy]

Vezerid - Thanks, sorry for my slow response, I have been puzzling over this
problem trying to figure it out. I have VLOOKUP and HLOOKUP formulas in this
spreadsheet and would like to figure out what is happening and keep them
rather than using a different formula. I used the "ISNUMBER" function to
check the reference cells, and the cells are values. I used "LEN" and they
have the same number of characters. One thing I did learn is that the
"HLOOKUP" function that is giving me N/A returns a value when I change the
range lookup to "TRUE" from "FALSE". Unfortunately, the value it is
returning is the one under the one I am looking for. The value I am looking
for, and is referenced in the formula, is 7.5%. It is returning a value
referenced from 7.25%, the closest value that is not greater than the one I
want. That indicates to me that the formula is seeing the 7.5% value in the
table as being greater than 7.5%. It is a number, and it looks like exactly
7.50%. The 7.50% is coming from a formula which is linked to an input cell
of 7.00%, and then it adds 0.0025 to create a range of percentages. The
formula has been working fine in the past, but now it seems to not recognize
the referenced value as exactly 7.50%.

I would greatly appreciate any insight or help. Thank you very much.

Terry McCoy

 

[vezerid]

Hmmm,

Maybe your lookup value is really 7.5000%. Is there a chance that the
header value is not really 7.5000% and it actually only shows as 7.5%?
If, for example the value is 7.49999910% it could show as 7.5%. And
then LOOKUP would match with the earlier value, 7.25%.

To test,

=C2=0.075

Is this TRUE?

HTH
Kostis

 

[Terry McCoy]

Vezerid - Thanks, that may be it, although I do not know why. I got the
formula to work by manually typing in the value "7.5%" I do not know why
that makes a difference. It was feeding off of another cell where it was
manually typed in as "7.5%". Even weirder, I changed the line of percent
values so they would feed off of where I had manually typed in "7.5%". I had
them set up as = the typed in cell, and then -.025; -.050; +.025; +.050.
That gave me a range of percent values from 7.0% to 8.0%, with the manually
typed one, 7.5% in the middle. When I changed my lookup value to 7.0%, the
formula would not recognize the 7.0% from the range of percent values. I had
to type it in manually, and then it would see it. I used the format painter
to try to make sure the percent range and value lookup are formatted the
same. Any ideas on why it is doing this? Anything I might try?

Thanks for your continued help and patience.

Terry McCoy.