{=AVERAGE(AND(B6:B15>=B3,B6:B15<B4),C6:C15)}

[deano]

11/01/06 10.0
11/02/06 10.0
11/03/06 10.0
11/04/06 10.0
11/05/06 10.0
11/06/06 10.0
11/07/06 10.0
11/08/06 10.0
11/09/06 10.0
11/10/06 10.0

the above range is placed in B6:B15

cell B3 contains 11/01/06
cell B4 contains 11/05/06

doing a simple average(C6:C9) produces 10
placing a conditional average if cells B6:B15 are between 11/01/06 and
11/05/06 , average corresponding cells in C6:C15 as in expression

{=AVERAGE(AND(B6:B15>=B3,B6:B15<B4),C6:C15)} produces an incorrect
result = 9.09

why is that ?

cheers,
deano

 

[Niek Otten]

Hi Deano,

Your formula averages 0 and c6:c9 correctly.
Easiest is to use one extra column:

=IF(AND(B6>=$B$3,B6<$B$4),C6,"")

and average that column

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| 11/01/06 10.0
| 11/02/06 10.0
| 11/03/06 10.0
| 11/04/06 10.0
| 11/05/06 10.0
| 11/06/06 10.0
| 11/07/06 10.0
| 11/08/06 10.0
| 11/09/06 10.0
| 11/10/06 10.0
|
| the above range is placed in B6:B15
|
| cell B3 contains 11/01/06
| cell B4 contains 11/05/06
|
| doing a simple average(C6:C9) produces 10
| placing a conditional average if cells B6:B15 are between 11/01/06 and
| 11/05/06 , average corresponding cells in C6:C15 as in expression
|
| {=AVERAGE(AND(B6:B15>=B3,B6:B15<B4),C6:C15)} produces an incorrect
| result = 9.09
|
| why is that ?
|
| cheers,
| deano
|

 

[paul.robinson]

Hi
AND(B6:B15>=B3,B6:B15<B4) can evaluate to 1 or 0.
AND(B6:B15>=B3,B6:B15<B4),C6:C15 is now 11 numbers (a 1 or 0 and 10
10's)
Your array formula, I think, will return the AND condition on the last
row (which is 0) averaged with the ten numbers. This average is 100/11
= 9.09090909...

Try
{=SUM(IF(B6:B15<B4,1,0)*IF(B6:B15>=B3,1,0)*C6:C15)/SUM(IF(B6:B15<B4,1,0)*IF(B6:B15>=B3,1,0))}

The top sum adds the tens meeting both criteria, which is 30. The
bottom sum counts the number of entries meeting the criteria, which is
3. So the average is 10.
Note that
{=AVERAGE(IF(B6:B15<B4,1,0)*IF(B6:B15>=B3,1,0)*C6:C15)}

will give 3, as the sum part will be 30, but you will have 10 numbers
in the range.

regards
Paul

 

[Bob Phillips]

Your formula is not evaluating the B6:B15 range as you think and returning
matches in C6:C15, but rather is averaging C6:C15, and the result of the AND
test (which returns FALSE), so it is averaging
(0,10,10,10,10,10,10,10,10,10,10), which is 100 divided by 11, QED.

The AND does not return an array of results, so you need something that does

AVERAGE(IF((B6:B15>=B3)*(B6:B15<B4),C6:C15))

as an array formula.




--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[deano]

Sorry for the late reply, I lost the broadband connection. on 12/6
12:38 AM, I asked,
12/6 3:50 AM , Niek Otten suggested to use one extra column and
average that column.
=IF(AND(B6>=$B$3,B6<$B$4),C6,"")

12/6 4:24 AM , Paul Robin suggested to
{=SUM(IF(B6:B15<B4,1,0)*IF(B6:B15>=B3,1,0)*C6:C15)/SUM(IF(B6:B15<B4,1,0)*IF(B6:B15>=B3,1,0))}

12/6 4:50 AM, Bob Phillips suggested to
{=AVERAGE(IF((B6:B15>=B3)*(B6:B15<B4),C6:C15))}

Thank you all for you prompt feedback, My broadband connection dropped
out otherwise I would have answered sooner. Any way

Niek, I would but I can not add a column.
Paul, yes that works.
Bob, for some reason, that still does not work.

I altered the data to make it more readily obvious. Data in C6:C15

B column C col D column
------------- ------- --------
11/01/06 Start day in B3
11/05/06 End day in B4

11/01/06 10.0 cats
11/02/06 5.0 dogs
11/03/06 3.0 cats
11/04/06 7.0 rats
11/05/06 10.0 dogs
11/06/06 5.0 cats
11/07/06 10.0 dogs
11/08/06 10.0 rats
11/09/06 7.0 cats
11/10/06 3.0 dogs


6.25 =AVERAGE(C6:C9)

0.00 {=AVERAGE(IF(AND(B6:B15>=B3,B6:B15<B4),C6:C15,))}

2.50 {=AVERAGE(IF((B6:B15>=B3)*(B6:B15<B4),C6:C15,))}

2.50 {=AVERAGE(IF(B6:B15>=B3,1,0)*IF(B6:B15<B4,1,0)*C6:C15)}

6.25
{=SUM(IF(B6:B15>=B3,1,0)*IF(B6:B15<B4,1,0)*C6:C15)/SUM(IF(B6:B15>=B3,1,0)*IF(B6:B15<B4,1,0))}


As you can see, Paul's suggestion works.

Part A:
Yet I still ask, How can an Average(IF( construct be made to work. The
reason I ask is that I have gotten to work before, just can not place
it in my sheets.

Part B:

suppose we add one additional compound criteria as column D has to be
cats to the date range criteria, the Sum IF construct works to sum and
to average, but again how could an Average( IF ( construct work with
compund criteria ?

Sum cats 13.0 =SUM(C6,C8)
SumIFcats 13.0 cats
{=SUM(IF(B6:B15>=B3,1,0)*IF(B6:B15<B4,1,0)*IF(D615=B34,1,0)*C6:C15)}

Here is the average
6.5
{=SUM(IF(B6:B15>=B3,1,0)*IF(B6:B15<B4,1,0)*IF(D615=D30,1,0)*C6:C15)/SUM(IF(B6:B15>=B3,1,0)*IF(B6:B15<B4,1,0)*IF(D615=D30,1,0))
}

hope that all makes sense. Looking forward to your replies.
Cheers,
deano

 

[Don Guillett]

Re-read Bob's post

 

[deano]

Re-read Bob's post

Terse....Don, but to the point. It was the extra comma at the end which
made it not work.

2.50 {=AVERAGE(IF((B6:B15>=B3)*(B6:B15<B4),C6:C15,))}
6.25 {=AVERAGE(IF((B6:B15>=B3)*(B6:B15<B4),C6:C15))}

it also works on compound criteria, beautiful......
6.5 {=AVERAGE(IF((B6:B15>=B3)*(B6:B15<B4)*(D615=D30),C6:C15))}

Thanks to you All.

cheers, deano