# 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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.

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

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?

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.

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.

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

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.

excellent. thanks for the help guys.

Replies
7
Views
462
Replies
2
Views
149
Replies
4
Views
274
Replies
2
Views
318
Replies
1
Views
275

1,218,808
Messages
6,144,602
Members
450,555
Latest member
sheldor

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back