# Unique averaging question from a newbie

#### dwrecipe

##### New Member
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!! 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)

### Excel Facts

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

##### MrExcel MVP
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
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!

##### MrExcel MVP
"Without helper columns..."

Much to be prefered!

#### dwrecipe

##### New Member
Thanks!! Both of your formulas are great! I really appreciate your help!

DW

Replies
3
Views
52
Replies
8
Views
101
Replies
10
Views
80
Replies
1
Views
103
Replies
2
Views
69