Excel #Value Error

[Guest]

I am getting a #Value error message when I enter in the following formula =sum(a1:iv1). The oddity of it all is that this formula is in a large amount of other cells. Some cells give me the error, some don't. Some cells have values and lables, although they may not return the error message. I thought that maybe the range was too long, but since the error is not consistant, I ruled that out. I have copied the formula to the cells that give me the #value error, but that has not remedied the issue. Any suggestions?

 

[Helen]

Somtimes Excel goes all funny. I had an example today where there were 2 values of 15, yet the sum came to 0.

the only way I have found to cure some of these problems is to EDIT/CLEAR/ALL.

I then retype the formula and it works.

Just a suggestion, and hope it works

Helen

 

[Guest]

On 2002-03-04 09:36, Anonymous wrote:
I am getting a #Value error message when I enter in the following formula =sum(a1:iv1). The oddity of it all is that this formula is in a large amount of other cells. Some cells give me the error, some don't. Some cells have values and lables, although they may not return the error message. I thought that maybe the range was too long, but since the error is not consistant, I ruled that out. I have copied the formula to the cells that give me the #value error, but that has not remedied the issue. Any suggestions?

I can't even get excel to accept that formula.

Try
=SUM(1:1)
instead.

 

[Anne Troy]

It seems to me that if you have values and labels in that range, you may want to consider a different spreadsheet layout. Can you describe your data layout??

 

[Chris Davison]

On 2002-03-04 09:36, Anonymous wrote:
I have copied the formula to the cells that give me the #value error, but that has not remedied the issue.

if you've copied it across, the IV1 reference will increase relatively and become IW1 or IX1 - these cells don't exist, you've fallen off the edge of the spreadsheet

try making the last bit $IV1, which will anchor it to the last column and not increment if you copy the formula any further right from where it already sits