Conditional Averages excel 2003

jeffw

New Member
Joined
Aug 2, 2007
Messages
5
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Diffy

Well-known Member
Joined
Dec 22, 2006
Messages
512
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

Well-known Member
Joined
Dec 22, 2006
Messages
512
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

New Member
Joined
Aug 2, 2007
Messages
5
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

Well-known Member
Joined
Dec 22, 2006
Messages
512
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

New Member
Joined
Aug 2, 2007
Messages
5
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

Well-known Member
Joined
Dec 22, 2006
Messages
512
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

New Member
Joined
Aug 2, 2007
Messages
5
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
 

jeffw

New Member
Joined
Aug 2, 2007
Messages
5
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
 

Forum statistics

Threads
1,181,363
Messages
5,929,538
Members
436,676
Latest member
Mavri

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top