Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Getting #ref error after row / cell is this a limit in excel 2003 tia sal2
Greetings All
sorry if this the second post the first one never made it through...gremlins in the system I知 getting a strange #ref error message in excel and I知 not sure why. Everything works great up to cell and row. but when I get past that the #ref error pops up. Is this a limit to excel 2003? Anyone have any workarounds or recommendations? The code I start to get the error at is =INDEX($D$3:$D$62,2*ROWS ($1:31)+COLUMNS($D:D)-2) An image can be found at http://test.onewithall.net/problem/excel_problem.jpg or the Excel file can be found at is http://test.onewithall.net/problem/excel_problem.xls Tia sal2 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting #ref error after row / cell is this a limit in excel 2003
You have a range of d3:D62 which is 60 cells. Yo can only have an index of
60 cells. You start to get the REF errors when the index becomes larger than 60. I went to the menu Tool - formula auditing - Evaluate formula to find the reson for the error. "please_post_to_groups" wrote: Greetings All sorry if this the second post the first one never made it through...gremlins in the system Im getting a strange #ref error message in excel and Im not sure why. Everything works great up to cell and row. but when I get past that the #ref error pops up. Is this a limit to excel 2003? Anyone have any workarounds or recommendations? The code I start to get the error at is =INDEX($D$3:$D$62,2*ROWS ($1:31)+COLUMNS($D:D)-2) An image can be found at http://test.onewithall.net/problem/excel_problem.jpg or the Excel file can be found at is http://test.onewithall.net/problem/excel_problem.xls Tia sal2 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting #ref error after row / cell is this a limit in excel 2003
Hi Mike
If I set the Format of the cells to be 'Number" with 2 decimals values a 3.22 / 3.61 / 2.74 / 2.78 / 1.00 / 0.79 Sums = 14.14 If I set the Format to Custom and use _(*#,##0);(#,##0);(*"-"?? etc values become: 3 / 4 / 3 / 3 / 1 / 1 ( I would like value to reflect 15) If i use =Sum(INT(D3:J3)) i get a value of 11???? if I use = Sum(ABS(D3:J3)) i get the value of 14.4 If I use =Sum(VALUE(D3:J3)) I get a value of 14 But I cannot seem to get a value of 15. thanks "please_post_to_groups" wrote: Greetings All sorry if this the second post the first one never made it through...gremlins in the system Im getting a strange #ref error message in excel and Im not sure why. Everything works great up to cell and row. but when I get past that the #ref error pops up. Is this a limit to excel 2003? Anyone have any workarounds or recommendations? The code I start to get the error at is =INDEX($D$3:$D$62,2*ROWS ($1:31)+COLUMNS($D:D)-2) An image can be found at http://test.onewithall.net/problem/excel_problem.jpg or the Excel file can be found at is http://test.onewithall.net/problem/excel_problem.xls Tia sal2 |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Getting #ref error after row / cell is this a limit in excel 2003tia sal2
You obviously didn't see Biff's or my reply. Hopefully you can see
this one. You are trying to access the 61st cell of a range which is only 60 cells long - this is why you are getting the #REF error. Hope this helps. Pete On Mar 21, 8:36*am, please_post_to_groups <please_post_to_groups@post_post_to_groups_please. com wrote: Greetings All sorry if this the second post the first one never made it through...gremlins in the system I知 getting a strange #ref error message in excel and I知 not sure why. *Everything works great up to cell and row. *but when I get past that the #ref error pops up. *Is this a limit to excel 2003? Anyone have any workarounds or recommendations? The code I start to get the error at is =INDEX($D$3:$D$62,2*ROWS ($1:31)+COLUMNS($D:D)-2) An image can be found athttp://test.onewithall.net/problem/excel_problem.jpg or the Excel file can be found at ishttp://test.onewithall.net/problem/excel_problem.xls Tia sal2 |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting #ref error after row / cell is this a limit in excel 2
I'm not following how this ties back the the first question.
=INDEX($D$3:$D$62,2*ROWS($1:31)+COLUMNS($D:D)-2) Your index range is fixed to the range D3:D62 so your last row cannot exceed 62. 2*31=62 fine, but you are then adding the value of the columns which range from 2 (D-2) up as your move to the right. So the smallest value for the COLUMNS($D:D)-2 is 2. 2*31+2=64 but your index range is a max of 62, hense Excel return REF. FYI - unrelated, change the last argument to COLUMNS($B:B) and drop the -2, this won't correct your REF problem, its just an improvement to you overall concept. Regarding your new question: Formatting has nothing to do with rounding, it is purely a display issue. So the question is what do each of these do?: =Sum(INT(D3:J3)) i get a value of 11???? This should give an error if not entered as an array. And if entered as an array it should give 11. The INT function returns the integer of each of the number and then the SUM kicks in. Maybe you should use =INT(SUM(D3:J3)) But that would return 14. if I use = Sum(ABS(D3:J3)) i get the value of 14.4 Again you should get an error if you don't enter this as an array. But ABS is designed to convert negative numbers to positive and leave the positive number unchanged, and that is not relevant here. If I use =Sum(VALUE(D3:J3)) I get a value of 14 You need to read the help system on this function like the others. It has no relevance in you case. Now the real question is what are you trying to do. If you want 15 it sounds like you want ROUNDUP =ROUNDUP(SUM(D3:J3),0) non-array. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Ali" wrote: Hi Mike If I set the Format of the cells to be 'Number" with 2 decimals values a 3.22 / 3.61 / 2.74 / 2.78 / 1.00 / 0.79 Sums = 14.14 If I set the Format to Custom and use _(*#,##0);(#,##0);(*"-"?? etc values become: 3 / 4 / 3 / 3 / 1 / 1 ( I would like value to reflect 15) If i use =Sum(INT(D3:J3)) i get a value of 11???? if I use = Sum(ABS(D3:J3)) i get the value of 14.4 If I use =Sum(VALUE(D3:J3)) I get a value of 14 But I cannot seem to get a value of 15. thanks "please_post_to_groups" wrote: Greetings All sorry if this the second post the first one never made it through...gremlins in the system Im getting a strange #ref error message in excel and Im not sure why. Everything works great up to cell and row. but when I get past that the #ref error pops up. Is this a limit to excel 2003? Anyone have any workarounds or recommendations? The code I start to get the error at is =INDEX($D$3:$D$62,2*ROWS ($1:31)+COLUMNS($D:D)-2) An image can be found at http://test.onewithall.net/problem/excel_problem.jpg or the Excel file can be found at is http://test.onewithall.net/problem/excel_problem.xls Tia sal2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting #ref error after row / cell is this a limit in excel 2003 tia sal2 | Excel Discussion (Misc queries) | |||
Getting #ref error after row / cell is this a limit in excel 2003 tiasal2 | Excel Discussion (Misc queries) | |||
Getting #ref error after row / cell is this a limit in excel 2003 tiasal2 | Excel Worksheet Functions | |||
Cell formatting error, Excel 2003 | Excel Discussion (Misc queries) | |||
Too Many Different Cell Format error in Excel 2003 | Excel Discussion (Misc queries) |