Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula doesn't work with negatives (Excel 97)

    Hi all,

    I got a while back a great formula from here, which is this:
    =SUM(OFFSET(Z9,0,MAX(0,MATCH(0,Z9:AG9,0)-4)):OFFSET(Z9,0,MATCH(0,Z9:AG9,0)-1))
    It basically sums up the changing numbers from a range. (don't ask me to explain you how the formula works, I'm sure any of you here could give Me lessons on what it does...). I am happy that it does what is supposed to do.
    I ran today across a problem, whereby in some cells I have negative numbers and now the formula returns me #NA.
    Could anyone adjust it to work with negatives as well? I would greatly appreciate it and it would make my weekend!

    Thanks a lot,

    K.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula doesn't work with negatives (Excel 97)

    What does this formula do? Is it an array formula? Do you have a link to the original post? Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formula doesn't work with negatives (Excel 97)

    Hi,
    Is there definitely a zero in one of the cells in the range Z9:AG9? If not then the match function will return the #NA error.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula doesn't work with negatives (Excel 97)

    Yes you are right. Thanks for the tip, it helped.
    The problem was exactly the lack of one zero in my range.

    K.

Posting Permissions

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