Conditional Averages excel 2003

[jeffw]

Hello all,

Im trying to create a formula to find the average of a list of numbers, based on a type of classification. I have three different classifications of workers (temp, permanent, and administrative) with a score of 1 to 5 in one column, and have 18 different rows (one row for every individual worker). I want to get the average of the scores, but i only want to take the average of the scores for each classification. For example, there are 6 temp workers, but instead of just putting in =average(A1:A6) I want to make some kind of function that measures the whole range of numbers, and takes the average IF the worker is temp, or permanent, or admin. I tried to do this with =AVERAGEA but I couldn't get it to work.

Any help would be greatly appreciated!

PS To further complicate things, Im doing all this in Spanish, so anyone who can give me the Excel formulas in Spanish will win the Gold Medal for "Excel Badass".

 

[Diffy]

Welcome to the board.

Can you post a small sample, with some expected results? I don't think it is quite clear what you are trying to do.

Do you want three different averages? A temp average, permanent average and admin average?

 

[Diffy]

Say A1:A10 has Either Temp, Perm, or Admin, and Say B1:B10 has your scores.

You could do something like:
=sumif(A1:A10,"Temp",B1:B10)/Countif(A1:A10,"Temp)

This will give you the average scores for those labeled as Temp.


Is this what you are trying to do?

 

[jeffw]

Hey Diffy,

Thanks for getting back to me. Here's a sample of what Im trying to do. Basically I want a formula for average score for each work category (admin, eventual, and permanente). But, Im going to be adding new entries, so I cant just do a simple =average for each worker type.

Let me know if this sample works or not.
Thanks!

Administrativa 5
Eventual de Campo 4
Eventual de Campo 4
Eventual de Campo 4
Eventual de Campo 4
Eventual de Campo 4
Eventual de Campo 4
Permanente de Campo 4
Permanente de Campo 4
Permanente de Campo 2
Permanente de Campo 1
Permanente de Campo 4

 

[Diffy]

I suggest you that you make a pivot table. That would be the easist way to get the averages for each.

Or you can easily make a table on a seperate sheet, list all the different types you are going to have in Column A Then in column B use a formula like this:

=sumif(Sheet1!A1:A10,A1,Sheet1!B1:B10)/Countif(Sheet1!A1:A10,A1)

 

[jeffw]

Thanks for your help Diffy!

Im having serious problems with the Spanish version of Excel. I think the sumif/countif would work perfectly (and I have no clue what a Pivot Table is...yeah, Im an Excel rookie) but I cant figure out the formulas in spanish. Oh well, that's it for today.

Thanks agian.

 

[Diffy]

jeffw, if you see this,

After looking into spanish excel try this, it is a bit longer but will do the trick:

=sumaproducto(--(A1:A10 = "Administrativa"),B1:B10)/sumaproducto(--(A1:A10="Administrativa"))

Its a longer formula, but it is the only thing I could find in spanish.

 

[jeffw]

Diffy, my friend,

YOU ARE THE MAN!!!! That worked perfectly. Thank you very much, that's a huge help. You win the Gold Medal for Excel Badass! Congratulations.

Thanks,
Jeff

 

[Aladin Akyurek]

=SUMAR.SI(Sheet1!$A$1:$A$10;A1;Sheet1!$B$1:$B$10)/CONTAR.SI(Sheet1!$A$1:$A$10;A1)

 

[jeffw]

Thanks Aladin! That works perfectly too.

One more question: how do I exclude any unanswered questions so that they are not included in the average as a zero? I have a few blanks that are being added in as zeroes.

Thanks