Can This Be Done in Excel 2007

[Aristotle33]

I have a spreadsheet. It has say 10 columns of data. Row 1 is the stock symbol. Row 2 is the stock price for 9/28/2011. Row 3 contains the stock price for 9/29/2011. I want to count the number of advancing stocks. I can set up a matrix to mirror this and put a formula in each cell to see if today's price is greater than yesterday's and then put a "1" in that cell and then sum them. This gets rather unwieldy with a large number of stocks.

Can I create a single formula that will go accross the array of prices, compare each pair and count if higher so that a single formula at the end of row 3 will compare the row 3 prices to row 2 and tell me how mnay of the 10 stocks advanced?

Thanks in advance

 

[doublej_jj]

Good question! Try this.

So B2:K2 is the stock price on day 1.
So B3:K3 is the stock price on day 2.

=Sumproduct((B3:K3>B2:K2)*1)

Jeff

 

[Aristotle33]

Wow, thanks alot! That worked perfectly and was easier than I expected.
I have a derivation of this that I also want to do but I'll see if I can figure it out now that I know where to start.

Thanks again!

 

[doublej_jj]

I love people that want to learn it themselves! Glad it helped.

Jeff

 

[Aristotle33]

My Excel books don't seem to be much help.

As a follow-up to this, can you explain how this works? I understand the basic function of sumproduct, it's the same as the array formula {=sum(B2:K2*B3:K3)}. I see that it works but I don't understand what your solution is doing in order to capture the items which are greater.

Second question, same scenario as before, but for the "advancers" I want to add the change for each one, i.e., capture advancing points. How would I do that?

Thanks,

Terry