multiple condition average

[Theadish]

A simplified version of my worksheet is this:

I have 2 columns of data. The first column is hour of the day going down. So it goes 1,2,3...23,24, then repeats for many days. The second column contains values. I need to average the values for hours 23-6. I was able to do an average(if()) array statement for hours 7-22, but I need to say average if hours<=6 OR hours >=23. I know ways to work around this by adding other columns if need be, but I'm hoping to do it all within a function.

 

[dcardno]

=SUM(NOT((hours>6) * (hours<23)) * values) / SUM(NOT((hours>6) * (hours<23)) * 1)

A little kludgy, it seems, but I had no luck with the AND or OR logical functions.
I am not sure that I got the ">" and "<" right - you might need ">=" and "<=" and the second sum of a string of truth values might be replaced with a "count" function.

 

[Theadish]

I used an average if array instead of sum / count but the NOT statement was exactly what I needed. Thanks for the help!

 

[Aladin Akyurek]

I used an average if array instead of sum / count but the NOT statement was exactly what I needed. Thanks for the help!

Could you post a sample 5 values from your Hours range?

 

[Theadish]

The problem is resolved but here's how my sheet is set up. It's basically like this:

Hour Price
1 5.67
2 5.66
3 5.64
4 5.66
5 7.65
6 8.29
7 7.65
8 7.65
9 7.65
10 7.65
11 7.65
12 7.65
13 7.65
14 5.34
15 4.84
16 4.92
17 5.85
18 7.48
19 7.65
20 7.65
21 7.32
22 5.58
23 4.48
24 7.65

I actually have other columns for year, month, and day as well. My goal was to get monthly averages for hours 7-22, and hours 23-6. I was able to say "average if hours >=7 AND hours <=22", and with dcardno's suggestion putting a NOT around that statement gave me 23-6.

 

[Aladin Akyurek]

The problem is resolved but here's how my sheet is set up. It's basically like this:

Hour Price
1 5.67
2 5.66
3 5.64
4 5.66
5 7.65
6 8.29
7 7.65
8 7.65
9 7.65
10 7.65
11 7.65
12 7.65
13 7.65
14 5.34
15 4.84
16 4.92
17 5.85
18 7.48
19 7.65
20 7.65
21 7.32
22 5.58
23 4.48
24 7.65

I actually have other columns for year, month, and day as well. My goal was to get monthly averages for hours 7-22, and hours 23-6. I was able to say "average if hours >=7 AND hours <=22", and with dcardno's suggestion putting a NOT around that statement gave me 23-6.

=AVERAGE(IF((HoursRange <= 6)+(HoursRange >= 23),PriceRange))

Confirmed with control+shift+enter.

 

[Theadish]

Ok so a + is equivalent to OR, and * is equivalent to AND?

 

[dcardno]

Ok so a + is equivalent to OR, and * is equivalent to AND?

Yes.
TRUE evaluates to 1, while FALSE evaluates to 0 (in Excel - in other languages, including VBA, TRUE evaluates to -1). The IF statement will evaluate IF(NumberValue,xx,yy) as FALSE if NumberValue = 0 (and so return yy), and TRUE if NumberValue <> 0 (and so return xx).

From that we can see that if either value is FALSE (evaluates to zero) then multiplying them will return zero, which in turn will evauate to FALSE - so multiplication is equivalent to AND. If either (or any) operator is TRUE (that is, <> 0) then the result when adding them will be <> 0 - which will evaluate as TRUE - so addition is equivalent to an OR statement.

 

[Theadish]

excellent. thanks for the help guys.