multiple condition average

Theadish

New Member
Joined
Jan 21, 2005
Messages
34
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.
 
Upvote 0
I used an average if array instead of sum / count but the NOT statement was exactly what I needed. Thanks for the help!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top