Formula doesn’t work with negatives (Excel 97)

Topic

New Reply

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.

Reply | Quote

Replies

What does this formula do? Is it an array formula? Do you have a link to the original post? Thanks! –Sam

Reply | Quote

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.

Reply | Quote

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

K.

Reply | Quote