# Conditional Averages excel 2003

#### jeffw

##### New Member
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".

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?

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?

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!

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

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)

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.

jeffw, if you see this,

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

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

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

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

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

Replies
19
Views
1K
Replies
1
Views
2K
Replies
14
Views
3K
Replies
1
Views
808
Replies
4
Views
630

1,221,383
Messages
6,159,539
Members
451,571
Latest member
Qwissy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back