Extracting the values matching to multiplr columns.
 

HI ,

Here is my problem. I want to put the average of the schedule variance
per resource/permonth on the column last column i.e.

Say I want to display the average % of schedule variance for a resource XYZ
in the month on June i.e. in cell D3 I want to display the average variance
of the resource XYZ for the month on June. Likewise in cell D6 I want to
display the average variance of the resource XYZ for the month of July. and
so on. For this I need to filter out the data MOnth wise first and then
resource wise and then take an average of the values coming against these two
criteria and display it.

This is really very urgent and would be great if someone will be able to
help me out with this.


A B C
D
1 Month Resource Schedule variance Average Variance
2 June X 0.00%
3 June XYZ 200.00%
4 June XYZ 0.00%
4 June Y 0.00%
5 July X -20.00%
6 July XYZ 200.00%
7 July XYZ 100.00%

Extracting the values matching to multiplr columns.
 

=AVERAGE(IF((A2:A7="June")*(B2:B7="XYZ"),C2:C7))

this is an array formula so commit with Ctrl-Shift-Enter

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"phoenix" wrote in message
...
HI ,

Here is my problem. I want to put the average of the schedule variance
per resource/permonth on the column last column i.e.

Say I want to display the average % of schedule variance for a resource
XYZ
in the month on June i.e. in cell D3 I want to display the average
variance
of the resource XYZ for the month on June. Likewise in cell D6 I want to
display the average variance of the resource XYZ for the month of July.
and
so on. For this I need to filter out the data MOnth wise first and then
resource wise and then take an average of the values coming against these
two
criteria and display it.

This is really very urgent and would be great if someone will be able to
help me out with this.


A B C
D
1 Month Resource Schedule variance Average Variance
2 June X 0.00%
3 June XYZ 200.00%
4 June XYZ 0.00%
4 June Y 0.00%
5 July X -20.00%
6 July XYZ 200.00%
7 July XYZ 100.00%