How do you write a dynamic IF statement in Excel?

[GoingUp]

I have a huge spreadsheet tracking team members and percentage of completed tasks. What I want is an IF statement that dynamically changes as the number of team members changes.

For example,

I have 10 rows of data.

Say Column A is Team number, and Column B is percentage of tasks complete.
The first 4 rows are members that belong to Team 1, the Second 4 rows are team members from team 2, the last 2 rows are team members from team 3.

Each of them has a different percentage complete for their tasks, which is in column B. What I want is an average of the teams overall percentage complete.

Heres the kicker, the teams are not yet completely set. People get switched from one team to another and their work moves with them. I am wasting a large amount of time right now switching the range of my cells. Currently my formula for the average of Team 1 is "=AVERAGE(B1:B4)"

What I want is an If statement that will scan the whole worksheet and wherever Team 1 is present, it will give me the average of the B column cells that are associated with team 1. Is this even possible in excel? I know I could do it in a regular programming language, but don't know if Excel is robust enough to do this.

Edit:

And the spreadsheet would look like this.
Team 1 100%
Team 1 100%
Team 1 50%
Team 1 25%
Team 2 50%
Team 2 30%
Team 2 40%
Team 2 100%
Team 3 30%
Team 3 50%

 

[dderolph]

I believe this could also be done in Access. And, I suspect it can in Excel but I can't tell you now how to do it. I'll play with that a little while and see whether I can come up with something.

Also, I get the impression your current procedure is more difficult than necessary. As I understand it, you're editing the formula when you have changes in the number of members on a team. But, if you just insert a row when adding member to a team, or delete a row when removing a member, the formula will automatically adjust. Note that you need to insert or delete above the last row for a team to make Excel work this way.

 

[PowerEngineer]

Not sure I know what you want here. An "IF" statement implies to me that you're thinking about a VB macro. I believe you can accomplish this with a cell formula too:

=SUMIF(A:A,"Team 1",B:B)/COUNTIF(A:A,"Team 1")

I think I have the formatting right, but you can check the descriptions for these two functions. SUMIF adds the numbers in column B when the corresponding entries in column A are equal to "Team 1". COUNTIF counts the number of entries in column A that are equal to "Team 1". The resulrt should be the average you are looking for, and it should always recalculate to take changes in column enties into account.

 

[GoingUp]

Originally posted by: PowerEngineer

Not sure I know what you want here. An "IF" statement implies to me that you're thinking about a VB macro. I believe you can accomplish this with a cell formula too:

=SUMIF(A:A,"Team 1",B:B)/COUNTIF(A:A,"Team 1")

I think I have the formatting right, but you can check the descriptions for these two functions. SUMIF adds the numbers in column B when the corresponding entries in column A are equal to "Team 1". COUNTIF counts the number of entries in column A that are equal to "Team 1". The resulrt should be the average you are looking for, and it should always recalculate to take changes in column enties into account.

That worked! Thanks!

I also need another formula with 2 if statements.

Its basically to count people in each team that have 100% of their work done.

Pseudo code would be

If A = "Team 1" and B = 100%
Count++

Anyone know how to do that? Thanks.

 

[GoingUp]

bump