Solved

I need an excel formula

Posted on 2011-09-28
6
127 Views
Last Modified: 2012-05-12
I need an excel formula to calculate the average number of days for a specific Project ID, and display the results only once. I cant tell you how bad a screwed up the syntax on this one!

See the attached for an example.
9-28-11.xlsx
0
Comment
Question by:wrt1mea
6 Comments
 
LVL 19

Expert Comment

by:akoster
ID: 36717309
Easiest is to use a pivot table, like this :  9-28-11.xlsx
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 36717318
In F2:
=IF(A2=A1,"",AVERAGEIF($A$2:$A$25,A2,$D$2:$D$25))
then copy down.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36717322
If I were you I'd do a pivot table. Project ID in the row field and (Average of) Days in the data field.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 36717340
Here's an example:

=AVERAGEIF(A2:A25,J2,D2:D25)

Where J2 is the project Id, which is matched with A2:A25, and the average based on that comes from the data in D2:D25

See example attached,


9-28-11.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36717344
Missed the refresh!
0
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 36717423
Perfect! thanks to tall for the help, but this is the better solution for this particular situtation
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question