Excel #Value Error

Mar 4th, 2002, 10:36 AM

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 · Mar 4th, 2002, 10:51 AM

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

Mar 4th, 2002, 10:52 AM

Quote:

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 · Mar 4th, 2002, 10:55 AM

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 · Mar 4th, 2002, 01:54 PM

Quote:

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

Mar 4th, 2002, 10:55 AM	#4
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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?? __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 4th, 2002, 10:36 AM	#1
Guest Posts: n/a	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?

Mar 4th, 2002, 10:51 AM	#2
Helen Board Regular Join Date: Feb 2002 Posts: 103	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