CountIf Excel function

March 31, 2010 at 07:37:35
Specs: Windows XP

I'm stumped trying to figure out a countif function in excel 2007. I have intraday data related to oil prices. My columns are set up as

B: Date
C: Time of day
D: High price

Date Time of day High Price
1/4/2010 9:00:00 AM 83.98
1/4/2010 9:10:00 AM 82.33
1/4/2010 9:20:00 AM 82.28
1/4/2010 9:30:00 AM 82.04
1/4/2010 9:40:00 AM 82.99
1/4/2010 9:50:00 AM 83.75 ...and so on

I need to count the number of days the high price of the day exceeded to high price of the previous day. I'm not sure if the countif function is even the correct formula to use, I keep getting a circular reference warning for every formula I try. Thanks for your help!

See More: CountIf Excel function

Report •

March 31, 2010 at 08:17:39

If the prices in this example are in cells D2 to D7,

	B		C		D
1	Date		Time of day	High Price
2	01/04/10	9:00:00 AM	83.98
3	01/04/10	9:10:00 AM	82.33
4	01/04/10	9:20:00 AM	82.28
5	01/04/10	9:30:00 AM	82.04
6	01/04/10	9:40:00 AM	82.99
7	01/04/10	9:50:00 AM	83.75
8			Total			3

then this array formula will count the number of times the day's high is greater than the previous day's high.
Enter this in any cell D8 in this example:

With your cursor inside the formula in the formula bar click Ctrl+Shift+Enter
Using all three keys together results in the formula looking like this:

Note: Entering the curly braces from the keyboard will not work.

Array formulas surrounded by {} test each row in turn. In this case there will be 4 IF() tests performed and the result of each (0 or 1) will be added by the SUM function.


Report •

March 31, 2010 at 08:20:35
re: I need to count the number of days the high price of the day exceeded the high price of the previous day.

How about this - this is just an idea, not a final solution.

In Column E, place a series of formulae similar to this:


The goal is to find the MAX for each daily list of "High Prices" and compare it to the MAX from the previous day's list.

You'll only get a 1 one for those days where the MAX value was higher than the previous day's MAX value.

Then just SUM that column.

Report •

Related Solutions

Ask Question