If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#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 |
#2
|
|||
|
|||
#N/A - "Value not available"
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 |
#3
|
|||
|
|||
#N/A - "Value not available"
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 |
#4
|
|||
|
|||
#N/A - "Value not available"
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 |
#5
|
|||
|
|||
#N/A - "Value not available"
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 |
#6
|
|||
|
|||
#N/A - "Value not available"
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 |
#7
|
|||
|
|||
#N/A - "Value not available"
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 |
Thread Tools | |
Display Modes | |
|
|