Averaging only if NameID is the same

[DrSues02]

You guys have been a great help in the past. Hopefully you can help me out with some problems I've been having.

I'm trying to average data for the past 3 years. My spreadsheet is setup like this.

Year, NameID, Salary, Average Salary

I have the spreadsheet sorted by NameID so most people in the database will have 3 entries right after another. How do I determine an average salary for the person across all 3 years?

I need the average salary to display across from the most recent date if that is possible as well i.e. if the latest salary date is from 2006, I would prefer that is entered into the row corresponding to 2006 and leave the 2004/2005 rows blank.

Also, some people might only have 2 years of data so keep that in mind as well.

I hope I'm clear with what I need and I'm really hoping you guys can help.

Thanks,

DrSues02

[duane]

with data in a2:c13 I put the following in d2 and copied down to d13

it leaves blank if salary for that year is blank, or there is aditional salary data for that id below

=IF(OR(C2=0,SUMPRODUCT(($B3:$B$13=B2)*($C3:$C$13>0))),"",SUMPRODUCT(($B$2:$B$13=B2)*($C$2:$C$13))/SUMPRODUCT(($B$2:$B$13=B2)*($C$2:$C$13>0)))

[Paul]

Good one, Duane. I had come up with:

Please Login or Register  to view this content.

Which does the trick, but leaves a #VALUE! error in cells that shouldn't contain an average. It's easy enough to error check, but that would make the formula even longer. It's also an array formula.

[DrSues02]

Still having a few problems..

The first formula is not figuring it out correctly.

Example:

2004, A, 0
2005, A, 40,000

This returns a blank for the first row (correct), but returns 40,000 for the second row when it should actually be 20,000 (two year average of 20,000).

I forgot to mention that some of the salaries will be 0.

Second formula,

I think this might work, but in order to make the example more simple, I didn't include some of the rows. There are actually several columns between the NameID and Salary columns full of additional information.

Could you tell me which part of the second formula to modify in order to grab the column that i need?

Also, is it relatively easy to change the formulas if I want to sum the salaries for example?

Thanks for the help guys.

[duane]

so a salary can be zero? Then we need to exclde ""s

Please Login or Register  to view this content.

to sum, just eliminate the /sumproduct portion - and add one )

[DrSues02]

Thanks, that second formula worked out perfectly. This has saved me TONS of time since I have over 2500 records.

Since you already understand the spreadsheet, I have an additional question.

Like I said, here is the spreadsheet again w/ additional info:

Year, NameID, Salary, Division

Most of the time, this works out fine. However, some people changed divisions during the course of the year.


Example:
2006, A, 20000, Division F
2006, A, 20000, Division C

Obviously, this has problems and will throw off the averages im trying to use. Is there any way to compensate for this in the formula?

Once again, I appreciate your guys help. You are definitely wizards with Excel.

[duane]

not sure what you mean by compensate for this. Do you only want to average the salary when in a given division? Presumably the divison is in column d. Given thta, I'd say this.

Please Login or Register  to view this content.

If either the current row salary is blank, or there is a repeat of the current row id and division below, the formula results in a blank, otherwise it averages the salary of the current row id and division.

[DrSues02]

Even if the person is in 2 divisions that year, I would like to have a yearly average rather than an average for each division. As the formula currently stands, it is taking each entry in 2006 as a separate year which throws the entire calculation off.

For example:

2006, A, 20000, Division F
2006, A, 20000, Division C

I would like this to return an average of 40,000.

I hope I'm clear with this. Thanks for all the help duane.

[duane]

you posted

Please Login or Register  to view this content.

yet now you say that the divsion should not affect the avergae, so the original formula should work, no?

[DrSues02]

It will have an impact because it is figuring out the average for 5 years instead of 4.

Example (simplied #'s)

2006, A, 37, Division A
2006, A, 61, Division B
2005, A, 118, Division A
2004, A, 99, Division A
2003, A, 104, Division A

The formula returns 84 for the average salary: 37+61+118+99+104 = 419/5 = 84
It is counting the two 2006 entries as 2 separate years rather than just 1.

However, since I'm trying to figure out yearly salary, it should read (37+61) +118 + 99 + 104 = 419/4 = 105



Sorry I wasn't clear before. I hope this illustration helps.

DrSues02

[duane]

The only way I can think of is to insert a column E (starting in row 2 as before) - I think this will work but you have the best test.

Please Login or Register  to view this content.

this will give a total for years which repeat for a given id (column b), but leave blank a year whose salary is blank

then

Please Login or Register  to view this content.