How execute formula using data from A1 to A... (until empty cell in B)

[elite_thut]

Hi,
I have tried searching for a solution to my problem, but I can't even figure out how to describe my issue simply enough to get relevant search results.

Please Login or Register  to view this content.

My formula in this case would be [sum(B1:B3)-sum(A1:A3)]/sum(A1:A3).
However, if there was a number at B4, then the formula would be [sum(B1:B4)-sum(A1:A4)]/sum(A1:A4).
If there was a number at B5, then the formula would be [sum(B1:B5)-sum(A1:A5)]/sum(A1:A5).
* There will never be a gab in between two rows. So B1=160, B2=140, B4=100 is impossible.

So how can I make a formula that will always work no matter if colum B goes all the way down to 2...6,7 or 8?

Thanks

[Jonmo1]

Try

=(SUM(B:B)-SUMIF(B:B,"<>",A:A))/SUMIF(B:B,"<>",A:A)

[Glenn Kennedy]

Hi there. try this:
=(SUM(B:B)-SUMIF(B:B,">"&0,A:A))/SUMIF(B:B,">"&0,A:A)

[elite_thut]

Ok, I didn't think it would matter for the answer, but in fact the range is not from A1:A9...it's from A35:A46. A1:A9 was only for my example.

[Jonmo1]

Try
=(SUM(B$35:B$46)-SUMIF(B$35:B$46,"<>",A$35:A$46))/SUMIF(B$35:B$46,"<>",A$35:A$46)

[Glenn Kennedy]

=(SUM(B35:B46)-SUMIF(B35:B46,"<>",A35:A46))/SUMIF(B35:B46,"<>",A35:A46)

will do that.

[Glenn Kennedy]

Jonmo, I'm slowing up. You beat me by a minute this time!!

[Jonmo1]

And I even typed more characters (in the formula)...Woo Hoo.

[elite_thut]

haha thanks guys.

Would you care to explain? <> is "no equal to". But cells in B and A are not equal even if the cells in B are not empty. So what does it check exactly?

[AliGW]

" < > " is shorthand for "is not blank".

Hope this helps!

[Glenn Kennedy]

"<>" means anything other than blank. Or you could use my original version >0... but that might not be as clever if zero values are possible.

[Glenn Kennedy]

...now 2 mins behind a latecomer to the field.

[elite_thut]

Oh I thought <> was "not equal to"... "not blank" makes more sense in the formula...

Thanks!

[AliGW]

@Glenn - big LOL!!!

[AliGW]

Oh I thought <> was "not equal to"... "not blank" makes more sense in the formula...

Thanks!

It does, and another way of putting it is "is not equal to blank"!!!