Average

[geo40]

Question:

In EXCEL I want a average of the costprice of the employees ( who still work at our company )per business unit. I've got the following fields:

Costplace Person Date of discharge Costprice

101 A 40
101 B 31-12-2001 50
101 C 60
201 D 50
201 E 100
201 F 60
201 G 31-12-2001 100
301 H 40
301 I 20

On a second sheet I want to calculate above mentioned in following lay-out:

Costplace Average costprice

101 50
201 70
301 30

What has to be the formula for this ??

 

[xlbo]

Array Formula again:
=SUM((sheet1!$A$1:$A$100=A1)*(sheet1!$C$1:$C$100="&quot*(sheet1!$D$1:$D$100))/SUM((sheet1!$A$1:$A$8=A1)*(sheet1!$C$1:$C$8="&quot)

where data on sheet1
Costplace in A etc etc
summarty on sheet 2 with costplaces starting in A1 Rgds
~Geoff~

 

[ETID]

Don't be afraid of the pivot table tool,...

It's designed to group and summarize data in a variety of different ways, including what you have described.


Try it out, it's free

 

[DaleWatson123321]

Hi geo,

I'd love to be able to demonstrate the power of Excel's "database functions" - e.g. =DAVERAGE. In your situation, I believe it's an EXCELLENT choice.

If you'd like, I could email you an example file, or if you prefer, you could email me your file, and I would enter the formulas directly into your file and return it.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca