| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
{=AVERAGE(AND(B6:B15=B3,B6:B15<B4),C6:C15)}
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
{=AVERAGE(AND(B6:B15=B3,B6:B15<B4),C6:C15)}
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 "deano" wrote in message ups.com... | | 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 | |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
{=AVERAGE(AND(B6:B15=B3,B6:B15<B4),C6:C15)}
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 deano wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
{=AVERAGE(AND(B6:B15=B3,B6:B15<B4),C6:C15)}
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" wrote in message ups.com... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
{=AVERAGE(AND(B6:B15=B3,B6:B15<B4),C6:C15)}
Sorry for the late reply, I lost the broadband connection. on 12/6
12:38 AM, I asked, 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 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(D6:D 15=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(D6:D 15=D30,1,0)*C6:C15)/SUM(IF(B6:B15=B3,1,0)*IF(B6:B15<B4,1,0)*IF(D6:D15 =D30,1,0)) } hope that all makes sense. Looking forward to your replies. Cheers, deano |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
{=AVERAGE(AND(B6:B15=B3,B6:B15<B4),C6:C15)}
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
| Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
| how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions | |||
Linear Mode
