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.
 

Some videos you may like

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
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Theadish

New Member
Joined
Jan 21, 2005
Messages
34
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Jan 21, 2005
Messages
34

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Jan 21, 2005
Messages
34

ADVERTISEMENT

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

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,846
Messages
5,544,634
Members
410,626
Latest member
rkmadasu
Top