consecutive positive or negative

kong

New Member
Joined
Aug 4, 2005
Messages
23
hi to everybody

I have 10 number (positive or negative) from A1 to J1....
I need a formula in A2 that count starting from A1 how many cell are positive (negative..depending of A1) consecutively

for ex.:
if : A1>0, B1>0, C1>0, D1<0-----> a2= 3

if: A1>0, B1<0, C1>0, D1>0 --------> a2=1 (starting from a1, consecutively by the sign of A1)

se: A1<0, B1<0, C1<0, D1<0, E1<0, F1>0, G1 >0------------> a2= -5

hope to be clear
thanks to all who can
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi Richard,

surely that will just give the total positive count minus the total negative count, and not the first consecutive of one sign?!?!?

This array formula ( entered using Ctrl-Shift-Enter ) will match the requirement:
Code:
=(MATCH(-SIGN(A1),SIGN(A1:J1),0)-1)*SIGN(A1)
entered using Ctrl-Shift-Enter.

Excel will put curly brackets around the formula when it is entered, to indicate that it's an array formula.

Remember to re-enter with Ctrl-Shift-Enter if you edit the formula for any reason.
 
Upvote 0
thankyou Richard we are quite near the solution
your formula work! , but i need something little different....i'll try to be clear as possible

if the sign of B1 is different from A1 the resullt in A2 is 1 ( i don't care if after B1 there are some sign consecutive) , only if the sign of B1 is = of the sign of A1 i go on to look if the sign of C1 is the same..and so on....so : if A1>0 , B1 <0 ---> i stop and the result in A2 is 1......only if : A1>0 , B1>0 i look C1>0? yes? ok the result is 3 ... no? the result in A2 is 2.....

thank a lot for your time
 
Upvote 0
Glenn

Oops - I seem to be making a habit of misreading (or misinterpreting) posts. Back to reading school for me, I think!

Richard
 
Upvote 0
I'm being seriously pedantic here, but a (very slight) simplification of Glenn's formula:

Code:
=MATCH(SIGN(A1),SIGN(A1:G1)*-1,0)-1

Again, confirmed with Ctrl+Shift+Enter.
 
Upvote 0
Errr, Richard, that gives positive counts for negative series, but OP said:
se: A1<0, B1<0, C1<0, D1<0, E1<0, F1>0, G1 >0------------> a2= -5

which is why I multiply by sign A1 afterwards.
 
Upvote 0
oooh God save the Queen

thankyou both
it work
I hope to be able to help you next time
bye
 
Upvote 0
Glenn

Errr, Richard, that gives positive counts for negative series, but OP said:
GlennUK

I refer you to my earlier post:

Oops - I seem to be making a habit of misreading (or misinterpreting) posts. Back to reading school for me, I think!
Me

:oops:

Hey, at least I'm being consistent
 
Upvote 0
Ha ha, yes you are. It must be one of those days.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top