Unique averaging question from a newbie

dwrecipe

New Member
Joined
Oct 3, 2006
Messages
4
Hi Everybody,

I am relatively new to Excel and trying to figure out a good way to automate some weather(temp) tracking. My questions (??) are found in the example below. I have the day of the month and the temp in seperate cell on my worksheet, but have them seperated here by a /.

I want to be able to have it calculate how long the positive and negative streaks (days in a row) usually last (number of days).

I would also like to be able to then find the average temperature broken out by days that are negative and days that have a positive temp.

Any help would be awesome!! :biggrin:

Thanks



Day of the month / Average Temperature
A/B
1/ 10
2/5
3/ 3
4/-10
5/-7
6/-15
7/-2
8/6
9/-7
10/-3
11/8
12/5
13/13
14/-4
15/3
16/1
17/9
18/15
19/5
20/8
21/11
22/4
23/6
24/-1
25/-3
26/8
27/8
28/12

Positive temperature's usually last ?? (This will be how long the positve ""streaks"" last on average in number of days)

Negative temperatures usually last ?? (This will be how long the negative ""streaks"" last on average in number of days)

Positive temperature average ?? (This will be the average of only the ""positive"" days)
Negative temperature average ?? (This will be the average of only the ""negative"" days)
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
hi - welcome to the board!

following uses a couple of 'helper columns':
Book1
ABCDEF
1BSignRun Counts
21013Average +ve temp7.333333
351 Average +ve run3
431 
5-10-14Average -ve temp-6
6-7-1 Average -ve run2.666667
7-15-1 
8-2-1 
9611
10-7-12
11-3-1 
12815
1351 
14131 
1541 
1661 
17-1-12
18-3-1 
19813
2081 
21121 
Sheet1


formula in b2 & copied down

=SIGN(A2)

...to give +ve / -ve flag

formula in c2 & copied down:

=IF(AND((B2<>B1),ISNUMBER(MATCH(-B2,B2:B21,0))),(MATCH(-B2,B2:B21,0))-1,IF(AND((B2<>B1),NOT(ISNUMBER(MATCH(-B2,B2:B21,0)))),COUNTIF(B2:B21,B2),""))
...core of this is

MATCH(-B2,B2:B21,0))-1

...which finds the position of the next change in sign on the list from the current position & takes one away from it. when this is done at the first instance of a run, it's effectively the count of the run length. re the rest of the formulas:

AND((B2<>B1),ISNUMBER(MATCH(-B2,B2:B21,0))),

...is to work out where the runs start, as long as it's not the last run:

AND((B2<>B1),NOT(ISNUMBER(MATCH(-B2,B2:B29,0)))),

...works out if it's the last run, and:

COUNTIF(B2:B21,B2),""))

...does a straight count for the last run as the match() approach won't work (it returns an na() in the last run)

in f2:
=AVERAGE(IF(B2:B21=1,A2:A21))

...entered with control + shift + enter, not just enter. rest are similar:

=AVERAGE(IF(B2:B21=1,C2:C21))
=AVERAGE(IF(B2:B21=-1,A2:A21))
=AVERAGE(IF(B2:B21=-1,C2:C21))
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,368
Without helper columns, and assuming that B2:B29 contains the data, try the following formulas which need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

Average positive run:

=AVERAGE(IF(FREQUENCY(IF(B2:B29>0,ROW(B2:B29)),IF(B2:B29<=0,ROW(B2:B29))),FREQUENCY(IF(B2:B29>0,ROW(B2:B29)),IF(B2:B29<=0,ROW(B2:B29)))))

Average negative run:

=AVERAGE(IF(FREQUENCY(IF(B2:B29<0,ROW(B2:B29)),IF(B2:B29>=0,ROW(B2:B29))),FREQUENCY(IF(B2:B29<0,ROW(B2:B29)),IF(B2:B29>=0,ROW(B2:B29)))))

Average positive temperature:

=AVERAGE(IF(B2:B29>0,B2:B29))

Average negative temperature:

=AVERAGE(IF(B2:B29<0,B2:B29))

Hope this helps!
 

dwrecipe

New Member
Joined
Oct 3, 2006
Messages
4
Thanks!! Both of your formulas are great! I really appreciate your help!

DW
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,444
Members
410,610
Latest member
renatha prado
Top