Unique averaging question from a newbie

[dwrecipe]

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)

 

[PaddyD]

hi - welcome to the board!

following uses a couple of 'helper columns':

Book1
	A	B	C	D	E	F
1	B	Sign	Run Counts
2	10	1	3		Average +ve temp	7.333333
3	5	1			Average +ve run	3
4	3	1
5	-10	-1	4		Average -ve temp	-6
6	-7	-1			Average -ve run	2.666667
7	-15	-1
8	-2	-1
9	6	1	1
10	-7	-1	2
11	-3	-1
12	8	1	5
13	5	1
14	13	1
15	4	1
16	6	1
17	-1	-1	2
18	-3	-1
19	8	1	3
20	8	1
21	12	1
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]

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!

 

[PaddyD]

"Without helper columns..."

Much to be prefered!

 

[dwrecipe]

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

DW