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

1. ## 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. ## 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

3. ## 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.

4. ## 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
•