conditional average - simple

[morag]

Should be fairly simple - but I'm having a mental blank today.

Two columns:

ColA ColB

red 3
blue 2
yellow 3
red 2
red 4

How do I calculate the average count for each colour (red should be 3) - I've got hundreds of different colours?

Thanks.

 

[VoG]

Try

=SUMIF(A1:A100,"red",B1:B100)/COUNTIF(A1:A100,"red")

Edit:

Or like this if you have many colours:

<b>Sheet4</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:32px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >red</td><td style="text-align:right; ">3</td><td > </td><td >red</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >blue</td><td style="text-align:right; ">2</td><td > </td><td >blue</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >yellow</td><td style="text-align:right; ">3</td><td > </td><td >yellow</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >red</td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >red</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E1</td><td >=SUMIF(A1:A100,D1,B1:B100)/COUNTIF(A1:A100,D1)</td></tr><tr><td >E2</td><td >=SUMIF(A2:A101,D2,B2:B101)/COUNTIF(A2:A101,D2)</td></tr><tr><td >E3</td><td >=SUMIF(A3:A102,D3,B3:B102)/COUNTIF(A3:A102,D3)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[orbea_adam]

=AVERAGE(IF((ColA)="red",ColB))

confirmed with ctrl+shift+enter
Adam

 

[fecurtis]

Using the sumif function. So lets say your data range is from cells A1:B200 and the colors are in column A and the numbers are in column B.

Get the sum of the numbers for each color:
=SUMIF(A1:B200, "red",B1:B200)
Do that for each color.

Get the sum of all of the values:
=SUM(B1:B200)

Put them together to get the average, so to get the average of all colors marked "red", use:
=(SUMIF(A1:B200, "red",B1:B200))/(SUM(B1:B200))

 

[orbea_adam]

fecurtis, I don't understand how your solution gives the average for red. Am I missing something? New math? Adam

Using the sumif function. So lets say your data range is from cells A1:B200 and the colors are in column A and the numbers are in column B.

Get the sum of the numbers for each color:
=SUMIF(A1:B200, "red",B1:B200)
Do that for each color.

Get the sum of all of the values:
=SUM(B1:B200)

Put them together to get the average, so to get the average of all colors marked "red", use:
=(SUMIF(A1:B200, "red",B1:B200))/(SUM(B1:B200))