Multi Conditional AverageIf Statement (excel) 2

[mattagain]

I am trying to create a short report from a list of data, I need to be able to quickly show weekly averages based on a specific team (i.e. if team=team1 and date is between start and end dates)

on my data sheet i have the following headings :-

Date / Agent / Team / Type Of Call / Quality Score / Accuracy Score

on the report sheet in cell A5 I have the start date and the end date in A7

also on the report sheet is the short report (cells C4:G7)

Team 1 Team 2 Team 3 Total
Quality
Accuracy
Totals

Any help would be appreciated because i am well and truly stuck

 

[Rofeu]

Probably the easiest way is to create a helper column that will list the date-interval/group needed (maybe something with =WEEKNUM([date])) and then use a pivottable.

If that won't work for you, please post back as it CAN be done with formulae.

Cheers,

Roel

 

[xlbo]

Pivottable would do this in about 2 seconds.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Take a look at the Pivot table Wizard...

Data > Pivottables & PivotCharts.

You could do that report in about 5 seconds.

Skip,

_[red][/red]

 

[SkipVought]

Well....

Geoff's about 150% faster than I am. ;-)

Skip,

_[red][/red]

 

[xlbo]

Skip I must learn that doing work quickly doesn't get you anywhere - it just gets you more work !

As Roel says, it can be done with formulae but from your description, a pivottable would be an ideal (and very quick and easy to update) solution

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff,

I guess that the caveat that I could TRY to wiggle thru, if you could squeeze thru a caveat, is that I said to the OP, "You could do that report in about 5 seconds."

<pompously>But, of course, IIIIIIIiiiiii could do to more than HALF as fast as THAT!!!! Naturally, it would be... [red]Half Fast![/red]"</pompously>


Skip,

_[red][/red]

 

[Rofeu]

a formula would look something like this:

=SUMPRODUCT(--(D2:D9=B14)*--(A2:A9=A14),C2:C9)/SUMPRODUCT(--((D2:D9)=B14)*--(A2:A9=A14))

with:
A2:A9 = Range with Teams listed
A14 = Team to look for
D29 = Range with i.e. the weeknumber of the entries
B14 = weeknumber to look for
C2:C9 = the values to average

You'll still need the helper column with this formula.

Cheers,

Roel

 

[mattagain]

thanks for the help so far, i have run into a further problem i dont have access to the weeknum function, (our it dept have mislaid the excel discs, is there anyway to do this without it?

 

[mattagain]

i have looked at doing this by running a lookup from the date field, but i dont know how to calculate the weeknum automatically, i could do it manually, however, it would mean having to re-calculate it all each time a new spreadhseet was started

 

[xlbo]

You could create your own date to week lookup table and reference that...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mattagain]

thanks guys, i have managed to get this working now, I found a formula for calculating week numbers

=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)

one again thanks to all involved