Hey guys and girls - I hope you can help me out with an Excel formula question…
I have a range of values in one column who at one point turn from positive to negative values. What formula can I use to calculate the number of this point, ie how many cells did it take starting at the top one before the values turned negative?
I realize that I could count the cells or do the highlight/drag and look up the range but we’re talking quite long lists here.
Any help would be greatly appreciated
=COUNTIF(A1:A6,">0")
Try that, but substituting the range of course.
If you know the data is ordered as you say, you can use
=countif(range you want counted,">0") or
=countif(range you want counted,">=0")
depending on whether you want to know when it becomes nonpositive or negative. This will count all the cells that are positive (or nonnegtive), but if they are ordered that will be fine
If they’re not ordered, you can sort them according to the value, and then do the countif.
COUNTIF will give the same answer regardless if the numbers are ordered or not. If gives the count of the cells that match the criterion. So there’s no reason to order the data before using COUNT IF.
The OP wanted to know the number of cells before the numbers changed from positive to negative. COUNTIF works in this case because the total number of positive cells is equal to the number of positive cells before the numbers turn negative. Had the cells been cyclic, for example, being + then - the + again and he’d wanted to know the numer before they first turn negative, both COUNTIF and ordering the data tehn using COUNTIF would give the same wrong answer.
Well, yes, I realize this (I did post the same answer), but I was just supplying some extra info. Now that I think about it more, it clearly doesn’t add anything. Oh well, at least I managed to post one thing that was (semi-)useful.
Thanks guys…I had a hunch it was fairly simple, but I just couldn’t figure it out.