Counting Increases and Decreases

[Agent86]

I have table in which every week a new set of numbers is inserted in the next available column to the right. At the top of each column I'd like to calculate two things ...

1. The number of cells that have a larger number than the prior week's number on the same row.

2. The number of cells that have a smaller number than the prior week's number on the same row.

I can't seem to figure our a formula that will do that. Any ideas? Perhaps a code solution?

Thank you for your help.

 

[shg]

You can use a formula like

=COUNTIF(range, "<" & someCell)

... but I don't undertsnd your data layout well enough to identify the range or the cell of interest.

 

[JoeMo]

Say last week's data is in cells C3:C100 and this week's data in cells D3:D100. Then in D1 enter this and confirm with ctrl + shift + enter (not just enter):

=SUM(IF($D$3:$D$100>$C$3:$C$100,1,0))

This will count the number of data points for this week that are greater than last week's companion point. In D2 enter the same formula except change the > sign to "<" (w/o the quotes) to get the number of points less than last week's companion points.

 

[Agent86]

Thanks shg4421. I'll try to do a better job of describing.

Currently my data range is G6:H463. Each row represents the value of a specific item. Each column is a week ending date. Essentially I'm trying to monitor how many items are increasing in value and how many are decreasing in value each week.

With that in mind, in H1 I'd like to calculate the number of cells in Column H that have a "larger" number than the number on their respective row in Column G. In H2 I'd like to calculate the number of cells in Column H that have a "smaller" number than the number on their respective row in Column G.

Every week another column will be added. So next week I will add data to Column I and look for increases and decreases compared to Column H.

Thank you for your assistance.

 

[Agent86]

JoMoe ... That works great. I've not used such a formula before. Thank you!

 

[shg]

Does this do what you want?

In H1: =SUMPRODUCT(--(H6:H500 > G6:G500))

In H2: =SUMPRODUCT(--(H6:H500 < G6:G500))

 

[Agent86]

Thank you shg4421. That works equally well. I've tried to use SUMPRODUCT on a few occasions, but have never been comfortable with it. Guess it's very worth my studying up on.

 

[shg]

It's very versatile; there's a good tutorial at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Agent86]

Excellent/fascinating link. Looking at it now. Thanks for the tip!