Successive increases/decrease count

[All2Cheesy]

Hi all,

I'm looking to create a count for the number of successive increases or decreases in my data set.
I'm not sure if this can be done, and I'm struggling to think of a way to get started on this. Would anyone have any suggestions? Example is below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]April
[/TD]
[TD]May
[/TD]
[TD]June
[/TD]
[/TR]
[TR]
[TD]Company 1
[/TD]
[TD]$150
[/TD]
[TD]$200
[/TD]
[TD]$300
[/TD]
[/TR]
[TR]
[TD]Company 2
[/TD]
[TD]$400
[/TD]
[TD]$200
[/TD]
[TD]$30
[/TD]
[/TR]
[TR]
[TD]Company 3
[/TD]
[TD]$400
[/TD]
[TD]$300
[/TD]
[TD]$600
[/TD]
[/TR]
</tbody>[/TABLE]

Company 1 should return +3 (Because it ended with 3 successive increases)
Company 2 should return -3 (Because it ended with 3 successive decreases)
Company 3 should return +1 (Because it ended with 1 increase)

 

[All2Cheesy]

*EDIT* The second formula isn't showing, but it essentially the same as the first formula, only in using < instead of >

I've created 3 formulas which achieve that I want (2 are hidden). However, they are very long, and at this point I would be looking for a way to condense them. Any suggestions?

Ex. Form1

=IF(AND(P2>O2,O2>N2,N2>M2,M2>L2,L2>K2,K2>J2,J2>I2,I2>H2,H2>G2,G2>F2,F2>E2),11,IF(AND(P2>O2,O2>N2,N2>M2,M2>L2,L2>K2,K2>J2,J2>I2,I2>H2,H2>G2,G2>F2),10,IF(AND(P2>O2,O2>N2,N2>M2,M2>L2,L2>K2,K2>J2,J2>I2,I2>H2,H2>G2),9,IF(AND(P2>O2,O2>N2,N2>M2,M2>L2,L2>K2,K2>J2,J2>I2,I2>H2),8,IF(AND(P2>O2,O2>N2,N2>M2,M2>L2,L2>K2,K2>J2,J2>I2),7,IF(AND(P2>O2,O2>N2,N2>M2,M2>L2,L2>K2,K2>J2),6,IF(AND(P2>O2,O2>N2,N2>M2,M2>L2,L2>K2),5,IF(AND(P2>O2,O2>N2,N2>M2,M2>L2),4,IF(AND(P2>O2,O2>N2,N2>M2),3,IF(AND(P2>O2,O2>N2),2,IF(AND(P2>O2),1,0)))))))))))

Ex. Form2

=IF(AND(P2<O2,O2<N2,N2<M2,M2<L2,L2<K2,K2<J2,J2<I2,I2<H2,H2<G2,G2<F2,F2<E2),11,IF(AND(P2<O2,O2<N2,N2<M2,M2<L2,L2<K2,K2<J2,J2<I2,I2<H2,H2<G2,G2<F2),10,IF(AND(P2<O2,O2<N2,N2<M2,M2<L2,L2<K2,K2<J2,J2<I2,I2<H2,H2<G2),9,IF(AND(P2<O2,O2<N2,N2<M2,M2<L2,L2<K2,K2<J2,J2<I2,I2<H2),8,IF(AND(P2<O2,O2<N2,N2<M2,M2<L2,L2<K2,K2<J2,J2<I2),7,IF(AND(P2<O2,O2<N2,N2<M2,M2<L2,L2<K2,K2<J2),6,IF(AND(P2<O2,O2<N2,N2<M2,M2<L2,L2<K2),5,IF(AND(P2<O2,O2<N2,N2<M2,M2<L2),4,IF(AND(P2<O2,O2<N2,N2<M2),3,IF(AND(P2<O2,O2<N2),2,IF(AND(P2<O2),1,0)))))))))))

Ex. Form3 (Shown)

=IF(AND(U2=0,V2=0),0,IF(U2=0,"-"&V2,"+"&U2))

 

[StephenCrump]

Here's a more general formula, with sample results. I'm guessing that you score 0 if value stays the same?

G2: =(COLUMN(F2)-COLUMN($B2)-MAX((COLUMN($B2:E2)+1-COLUMN($B2))*(SIGN($C2:F2-$B2:E2)<>SIGN(F2-E2))))*SIGN(F2-E2)

Excel 2010

	A	B	C	D	E	F	G
Company 1
Company 2
Company 3
Company 4
Company 5
Company 6
Company 7
Company 8

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]April[/TD]
[TD="align: right"]May[/TD]
[TD="align: right"]June[/TD]
[TD="align: right"]July[/TD]
[TD="align: right"]August[/TD]
[TD="align: right"]Score[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$150[/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]$300[/TD]
[TD="align: right"]$350[/TD]
[TD="align: right"]$400[/TD]
[TD="align: right"]+4[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$400[/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]$30[/TD]
[TD="align: right"]$20[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]-4[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$400[/TD]
[TD="align: right"]$300[/TD]
[TD="align: right"]$600[/TD]
[TD="align: right"]$650[/TD]
[TD="align: right"]$600[/TD]
[TD="align: right"]-1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]$200[/TD]
[TD="align: right"]$175[/TD]
[TD="align: right"]$175[/TD]
[TD="align: right"]$180[/TD]
[TD="align: right"]$185[/TD]
[TD="align: right"]+2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$200[/TD]
[TD="align: right"]$175[/TD]
[TD="align: right"]$190[/TD]
[TD="align: right"]$180[/TD]
[TD="align: right"]$170[/TD]
[TD="align: right"]-2[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]$200[/TD]
[TD="align: right"]$175[/TD]
[TD="align: right"]$190[/TD]
[TD="align: right"]$180[/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]+1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$200[/TD]
[TD="align: right"]$175[/TD]
[TD="align: right"]$190[/TD]
[TD="align: right"]$180[/TD]
[TD="align: right"]$180[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]$200[/TD]
[TD="align: right"]$180[/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]$180[/TD]
[TD="align: right"]$160[/TD]
[TD="align: right"]-2[/TD]

</tbody>

Sheet1​

<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2,j2<i2,i2<h2,h2<g2,g2<f2,f2<e2),11,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2,j2<i2,i2<h2,h2<g2,g2<f2),10,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2,j2<i2,i2<h2,h2<g2),9,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2,j2<i2,i2<h2),8,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2,j2<i2),7,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2),6,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2),5,if(and(p2<o2,o2<n2,n2<m2,m2<l2),4,if(and(p2<o2,o2<n2,n2<m2),3,if(and(p2<o2,o2<n2),2,if(and(p2<o2),1,0)))))))))))[ code]

</o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2,j2<i2,i2<h2,h2<g2,g2<f2,f2<e2),11,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2,j2<i2,i2<h2,h2<g2,g2<f2),10,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2,j2<i2,i2<h2,h2<g2),9,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2,j2<i2,i2<h2),8,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2,j2<i2),7,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2,k2<j2),6,if(and(p2<o2,o2<n2,n2<m2,m2<l2,l2<k2),5,if(and(p2<o2,o2<n2,n2<m2,m2<l2),4,if(and(p2<o2,o2<n2,n2<m2),3,if(and(p2<o2,o2<n2),2,if(and(p2<o2),1,0)))))))))))[>

 

[All2Cheesy]

The count resets back to 0 if there are two values which are the same. (This is every rare).

But that formula looks like it might just about do what I need.