Hello, 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 priceDate 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!

Hi, 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:=SUM(IF(D2:D6>D3:D7,1,0))

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:{=SUM(IF(D2:D6>D3:D7,1,0))}

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.

Regards

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

similarto this:=IF(MAX(D8:D13)>MAX(D2:D7),1,"")

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.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History