#N/A - "Value not available"

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

Hi Terry,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Terry McCoy" wrote in message ...
| 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 - Thanks for replying. The formula is:
=HLOOKUP($D44,$J$54:$N$64,F6+1,FALSE)

The input values in the table a 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.



"Niek Otten" wrote:

Hi Terry,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Terry McCoy" wrote in message ...
| 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,

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


On Jun 19, 4:15 pm, Terry McCoy
wrote:
Niek - Thanks for replying. The formula is:
=HLOOKUP($D44,$J$54:$N$64,F6+1,FALSE)

The input values in the table a 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.

"Niek Otten" wrote:
Hi Terry,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Terry McCoy" wrote in ...
| 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

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" wrote:

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


On Jun 19, 4:15 pm, Terry McCoy
wrote:
Niek - Thanks for replying. The formula is:
=HLOOKUP($D44,$J$54:$N$64,F6+1,FALSE)

The input values in the table a 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.

"Niek Otten" wrote:
Hi Terry,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Terry McCoy" wrote in ...
| 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

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

On Jun 20, 6:39 pm, Terry McCoy
wrote:
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" wrote:
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

On Jun 19, 4:15 pm, Terry McCoy
wrote:
Niek - Thanks for replying. The formula is:
=HLOOKUP($D44,$J$54:$N$64,F6+1,FALSE)

The input values in the table a 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.

"Niek Otten" wrote:
Hi Terry,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Terry McCoy" wrote in ...
| 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

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.

"vezerid" wrote:

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

On Jun 20, 6:39 pm, Terry McCoy
wrote:
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" wrote:
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

On Jun 19, 4:15 pm, Terry McCoy
wrote:
Niek - Thanks for replying. The formula is:
=HLOOKUP($D44,$J$54:$N$64,F6+1,FALSE)

The input values in the table a 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.

"Niek Otten" wrote:
Hi Terry,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Terry McCoy" wrote in ...
| 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