LinkBack URL
About LinkBacks
Hi,
I have a long list of numbers in column A. Can someone please suggest a formula that will count the number of times that a negative value in column A is followed immediately by another negative value?
Thanks!
Hi,
I have a long list of numbers in column A. Can someone please suggest a formula that will count the number of times that a negative value in column A is followed immediately by another negative value?
Thanks!
Last edited by andrewc; 12-14-2012 at 07:33 AM.
One simple way is this.
Assuming your data start in A2, in B2 and copy down put this.
=IF(AND(A2<0,A3<0),1,"")
This will be a helper and hidden-if you like- column.
Then use =COUNTIF(B2:B100,1)
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Thanks for the quick response!
Rather than using a helper column, is it possible instead to do this using a single array formula?
Probably. I'll take a look on this, BUT always my suggestion is to use a helper column using simple formula, rather than complicate array formula, special if you have many many rows.
=countif($a$1:$a$999"<0")
Thi will do what you want, but pls, think my suggestion in my previous post.
=MAX(FREQUENCY(IF(A2:A100<0,ROW(A2:A100)),IF(A2:A100>=0,ROW(A2:A100))))
This is an Array formula.
Thank you.
I would be grateful to see how to handle such a problem using an array if anyone has suggestions
Thanks!
Wow, that's great, thank Fotis!
You are welcome.
Thanks for the reb*
If your numbers are in A1:A25, how about:
=SUMPRODUCT(--(A1:A24<0),--(A2:A25<0))
or just
=SUMPRODUCT((A1:A24<0)*(A2:A25<0))
Cheers,
RD
Last edited by rda51; 12-18-2012 at 09:17 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks