# multiple condition average

##### New Member
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.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### dcardno

##### Well-known Member
Code:
``=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.

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

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

##### New Member

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.

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

##### New Member

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

#### dcardno

##### Well-known Member
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.

##### New Member
excellent. thanks for the help guys.

Replies
4
Views
197
Replies
1
Views
104
Replies
3
Views
179
Replies
2
Views
206
Replies
4
Views
98