by monthy avg calc if met criteria in diferent cell

rereis

New Member
Joined
Oct 9, 2006
Messages
35
.

Hello all I have the following xls:
------------A----------------B------------- C
1------- Date------- ----Ord#------- index
2------- 01/01/06-------W333-------7
3------- 01/03/06-------W332-------5
4------- 02/09/06-------W222------- 6
5-----------------------------------
6----------------------------
7----------- ---------------Jan-06-------Feb06
8------- Satisfied
9------- Dissatisfied

In row 8 I need to avg all index that are > than 6 for the month specified in row 7 and in row 9 all that are <=6.



Any suggestion?
Thanks
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Assuming B7 is the 1st of the relevant month formatted as "mmm-yy"

in B8

=AVERAGE(IF($C$2:$C$4>6,IF($A$2:$A$4-DAY($A$2:$A$4)+1=B$7,$C$2:$C$4)))

confirmed with CTRL+SHIFT+ENTER and copied across. Similar for row 9
 

rereis

New Member
Joined
Oct 9, 2006
Messages
35
it trows a #Value error for the first IF. it appear that if doesn't like stuff like C2:C4>6
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
See this illustration
Book3
ABCD
1DateOrd#
201/01/2006W3337
301/03/2006W3325
402/09/2006W2226
5
6
7Jan-06Feb-06
8Satisfied7#DIV/0!
9Dissatisfied56
10
Sheet1


Note: B7 and B8 are dates formatted as mmm-yy - must be 1st of month. Formulas need to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.
 

rereis

New Member
Joined
Oct 9, 2006
Messages
35
Thanks Barry,

You did the trick I did not know about CTRL+SHIFT+ENTER

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,114,402
Messages
5,547,748
Members
410,810
Latest member
thepinkbird
Top