Excel 2000 - Help with Countif needed

[Sean Cunningham]

I'm running Excel 2000

I'm trying to retrieve information from column 3 based on Column 2

Column 1 contains dates only - call it Day zero

Column 2 gives the percentage change in an index for the next day following
day zero

column 3 gives the percentage the index has changed for day 2 following Day
1

For example the last row shows the index rose 8.31% at the end of the first
day, then on the second day it fell 9.2% - so from day zero it fell just
0.89% to the end of the second day.

I'm looking to answer the question - how many times in the most recent 10
occasions having a positive percentage gain at the end of Day 1, have I had
another positive percentage gain at the end of day 2?

I have used the formula

=COUNTIF(OFFSET(INDEX($AM$406:$AM$803,COUNTIF($AN$406:$AN$803,">0")),-9,1,10,1),">0")

This returns "7" - counting just the 7 times the Day1 column is positive -
The answer I'm looking for is "2" as only the rows 20/12/2007 and
31/03/2008 had Day 1 and Day 2 both positive.

Once I get this solved I hope also to reuse the formula to sum the results
of the Day2 column. Namely to arrive at 2.76% being (2.29% + 0.47%)

There is another related question I am also hoping to solve. Is it possible
to also count back the last 10 occasions that I have had a positive day 1
followed by another positive Day 2 and sum the day 2 performance only?

AM AN AO

Day zero Day1 Day2

18/05/2007 2.43% 1.52%
27/06/2007 0.14% -1.27%
02/07/2007 0.74% 0.46%
12/07/2007 0.14% -1.71%
19/07/2007 -3.62% -0.19%
31/08/2007 1.98% -2.58%
06/09/2007 -4.32% -1.51%
26/09/2007 1.21% -2.12%
01/10/2007 1.75% -1.41%
04/10/2007 3.79% -1.14%
20/12/2007 4.71% 2.29%
13/02/2008 -4.60% -1.05%
20/02/2008 -3.87% -0.24%
25/02/2008 1.93% -0.25%
31/03/2008 6.59% 0.47%
12/05/2008 0.99% -0.21%
16/06/2008 -1.13% -1.58%
18/09/2008 8.31% -9.20%


Thank you in advance for any help you can provide


Sean Cunningham

 

[Sean Cunningham]

Thank you so much for your help.
I thought you'd be modifying or correcting my formula but you've come up
with a completely different way. Amazing - I'm really grateful.

One thing I should make clear is that the area AM406 to AO803 is populated
via a simple reference formula from elsewhere in the spreadsheet.
I take the raw data in from a database and I never know in advance how many
rows it will fill.
AM406 just checks to see if the reference cell has an entry or date in it.
If it has then it knows it will have values for AN406 and AO406.
Essentially AN406 would have the formula =IF(AM406<>"",B406,"")
and AO406 would have =IF(AM406<>"",C406-B406,"")
I copy these formulas all the way down to row 803, as I don't envisage I'll
be importing data with more rows that that.



With respect to the array formula
=SUM(($AN406:$AN491>0)*($AO406:$AO491>0)) it works fine, in that it gives
me the count of all the times that Day1 and Day2 are both together
positive. However I get different values if I select $AN406:$AN803 - Seems
the blank cells get added also.
Is there a way for the formula to ignore the blank cells?

By the way the second formula you gave
=SUMPRODUCT(-($AO$406:$AO$803>0),-($AO$406:$AO$803>0),($AO$406:$AO$803))
works a charm and returns the right answer.

Is there a way I can instruct a cell to query just the most recent 10 rows
without having to manually select those rows? The data is sorted in
assending order so I would always have 10 rows to work with if the data
ended anywhere between row 416 and row 803.

Your answer to question 3 is very neat. I don't understand enough about
functions to know how it works, but it does.

Thank you again for your assistance.


Sean Cunningham

 

[Sean Cunningham]

Sean Cunningham wrote:
[snipped]

With respect to the array formula
=SUM(($AN406:$AN491>0)*($AO406:$AO491>0)) it works fine, in that it gives
me the count of all the times that Day1 and Day2 are both together
positive. However I get different values if I select $AN406:$AN803 - Seems
the blank cells get added also.
Is there a way for the formula to ignore the blank cells?

I'm not seeing any problems with blank cells. Are you saying AN492:AN803
are blank and the two formulae above return different results?

Yes - every blank row included in the array increments the value by 1. I
said "cell" above but actually I mean each blank row adds one to the total.

With the data I have the formula above returns 38. If I change all
instances of $AN491 to $AN803 the formula returns 350
There are 312 blank rows in the latter array and 350 = 38+312



Hope this helps

Sean