Getting #ref error after row / cell is this a limit in excel 2003 tia sal2

[please_post_to_groups]

Greetings All

sorry if this the second post the first one never made it through...gremlins in the system

I’m getting a strange #ref error message in excel and I’m 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)-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

 

[Joel]

You have a range of d362 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.

 

[Ali]

Hi Mike
If I set the Format of the cells to be 'Number" with 2 decimals values are:
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

 

[Pete_UK]

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

 

[Shane Devenshire]

I'm not following how this ties back the the first question.

=INDEX($D$3:$D$62,2*ROWS($1:31)+COLUMNS($D)-2)

Your index range is fixed to the range D362 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)-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.