Hi,
I'm working in excel 2002. I have a table of data in sheet1, and am trying to perform calculations across them in sheet2. The whole table of data is named all, the first column is named dates, then in sheet2 i have 2 cells specifying dates start and end.
The calculations in the second sheet which seems to have a problem is:
=(PERCENTILE(INDIRECT(ADDRESS(MATCH(start,Sheet1!dates,1),4)):INDIRECT(ADDRESS(MATCH(end, sheet1!dates,1),4)),0.9))*100
which returns #NUM! error. In the define name dialog box all named ranges have the correct sheets referenced. So actually in the above formula when i hit enter the Sheet1! parts get replaced by the name of the workbook.xls
Also in the second sheet i have got other formulas which reference the all table without specifying the sheet at all - and that works, but makes no difference when i just use dates in the above formula. So i guess it has something to do with the INDIRECT, ADDRESS and MATCH functions.
Any ideas?
I'm working in excel 2002. I have a table of data in sheet1, and am trying to perform calculations across them in sheet2. The whole table of data is named all, the first column is named dates, then in sheet2 i have 2 cells specifying dates start and end.
The calculations in the second sheet which seems to have a problem is:
=(PERCENTILE(INDIRECT(ADDRESS(MATCH(start,Sheet1!dates,1),4)):INDIRECT(ADDRESS(MATCH(end, sheet1!dates,1),4)),0.9))*100
which returns #NUM! error. In the define name dialog box all named ranges have the correct sheets referenced. So actually in the above formula when i hit enter the Sheet1! parts get replaced by the name of the workbook.xls
Also in the second sheet i have got other formulas which reference the all table without specifying the sheet at all - and that works, but makes no difference when i just use dates in the above formula. So i guess it has something to do with the INDIRECT, ADDRESS and MATCH functions.
Any ideas?