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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

kong

New Member
Joined
Aug 4, 2005
Messages
23
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Glenn

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

Richard
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

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.
 

kong

New Member
Joined
Aug 4, 2005
Messages
23
oooh God save the Queen

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

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,141,756
Messages
5,708,329
Members
421,565
Latest member
Lastadiego

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
Top