Excel #Value Error
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Excel #Value Error

  1. #1
    Guest

    Default

     
    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?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Guest

    Default

    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.

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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
    :: Pharma Z - Family drugstore ::

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com