| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average only with specific critera
Hi. I am trying to average a set of numbers but only if the number in the
column to the left is equal to 1. For example: b1:b100 I have either the number 1, 2, 3, or 4, c1:c100 I have percentages I want to average only the percentages that have a 1 to the left of it in the b column. Thanks in advance... -- Thanks! Stephen |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average only with specific critera
Try this:
=SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1) Does that help? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Hi. I am trying to average a set of numbers but only if the number in the column to the left is equal to 1. For example: b1:b100 I have either the number 1, 2, 3, or 4, c1:c100 I have percentages I want to average only the percentages that have a 1 to the left of it in the b column. Thanks in advance... -- Thanks! Stephen |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average only with specific critera
You can use the following:
=SUMPRODUCT(--(B1:B100=1),C1:C100)/COUNTIF(B1:B100,1) Hope this helps, Miguel. "Stephen" wrote: Hi. I am trying to average a set of numbers but only if the number in the column to the left is equal to 1. For example: b1:b100 I have either the number 1, 2, 3, or 4, c1:c100 I have percentages I want to average only the percentages that have a 1 to the left of it in the b column. Thanks in advance... -- Thanks! Stephen |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average only with specific critera
Thank you, I did get it to work.
How would I do it if I want to average the percentages if either 1 or 2 were in the b column? Thanks in advance. -- Thanks! Stephen "Ron Coderre" wrote: Try this: =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1) Does that help? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Hi. I am trying to average a set of numbers but only if the number in the column to the left is equal to 1. For example: b1:b100 I have either the number 1, 2, 3, or 4, c1:c100 I have percentages I want to average only the percentages that have a 1 to the left of it in the b column. Thanks in advance... -- Thanks! Stephen |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average only with specific critera
Try this:
=SUMPRODUCT(SUMIF(B1:B100,{1,2},C1:C100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2})) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Thank you, I did get it to work. How would I do it if I want to average the percentages if either 1 or 2 were in the b column? Thanks in advance. -- Thanks! Stephen "Ron Coderre" wrote: Try this: =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1) Does that help? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Hi. I am trying to average a set of numbers but only if the number in the column to the left is equal to 1. For example: b1:b100 I have either the number 1, 2, 3, or 4, c1:c100 I have percentages I want to average only the percentages that have a 1 to the left of it in the b column. Thanks in advance... -- Thanks! Stephen |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average only with specific critera
Hi. The problem is that I have a column for each number on the left side.
For example: b1:b100 - 1, 2, 3, or 4 c1:c100 - percentages that work with 1 or 3 d1:d100 - percentages that work with 2 or 4 I want to be able to average the percentages that correlate with 1 and 2. So when I did the formula I used (c1:d100) and when C was 1, it would average C and D, instead of just c. As D would have been blank it averaged C and 0. Is there a way for the formula to ignore blanks? Thanks again. -- Thanks! Stephen "Ron Coderre" wrote: Try this: =SUMPRODUCT(SUMIF(B1:B100,{1,2},C1:C100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2})) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Thank you, I did get it to work. How would I do it if I want to average the percentages if either 1 or 2 were in the b column? Thanks in advance. -- Thanks! Stephen "Ron Coderre" wrote: Try this: =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1) Does that help? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Hi. I am trying to average a set of numbers but only if the number in the column to the left is equal to 1. For example: b1:b100 I have either the number 1, 2, 3, or 4, c1:c100 I have percentages I want to average only the percentages that have a 1 to the left of it in the b column. Thanks in advance... -- Thanks! Stephen |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average only with specific critera
Maybe one of these?:
=(SUMPRODUCT((B1:B100=1)*C1:C100)+SUMPRODUCT((B1:B 100=2)*D1:D100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2})) or =SUMPRODUCT((B1:B10={1,2})*C1:D10)/SUMPRODUCT(COUNTIF(B1:B10,{1,2})) Does that help? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Hi. The problem is that I have a column for each number on the left side. For example: b1:b100 - 1, 2, 3, or 4 c1:c100 - percentages that work with 1 or 3 d1:d100 - percentages that work with 2 or 4 I want to be able to average the percentages that correlate with 1 and 2. So when I did the formula I used (c1:d100) and when C was 1, it would average C and D, instead of just c. As D would have been blank it averaged C and 0. Is there a way for the formula to ignore blanks? Thanks again. -- Thanks! Stephen "Ron Coderre" wrote: Try this: =SUMPRODUCT(SUMIF(B1:B100,{1,2},C1:C100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2})) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Thank you, I did get it to work. How would I do it if I want to average the percentages if either 1 or 2 were in the b column? Thanks in advance. -- Thanks! Stephen "Ron Coderre" wrote: Try this: =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1) Does that help? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Hi. I am trying to average a set of numbers but only if the number in the column to the left is equal to 1. For example: b1:b100 I have either the number 1, 2, 3, or 4, c1:c100 I have percentages I want to average only the percentages that have a 1 to the left of it in the b column. Thanks in advance... -- Thanks! Stephen |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average only with specific critera
You can also use the ARRAY formula which must be entered with
ctrl+shift+enter vs just enter =AVERAGE(IF(B2:B100=1,C2:C100)) -- Don Guillett SalesAid Software "Stephen" wrote in message ... Hi. I am trying to average a set of numbers but only if the number in the column to the left is equal to 1. For example: b1:b100 I have either the number 1, 2, 3, or 4, c1:c100 I have percentages I want to average only the percentages that have a 1 to the left of it in the b column. Thanks in advance... -- Thanks! Stephen |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average only with specific critera
Yes, thanks everyone for the help!
-- Thanks! Stephen "Ron Coderre" wrote: Maybe one of these?: =(SUMPRODUCT((B1:B100=1)*C1:C100)+SUMPRODUCT((B1:B 100=2)*D1:D100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2})) or =SUMPRODUCT((B1:B10={1,2})*C1:D10)/SUMPRODUCT(COUNTIF(B1:B10,{1,2})) Does that help? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Hi. The problem is that I have a column for each number on the left side. For example: b1:b100 - 1, 2, 3, or 4 c1:c100 - percentages that work with 1 or 3 d1:d100 - percentages that work with 2 or 4 I want to be able to average the percentages that correlate with 1 and 2. So when I did the formula I used (c1:d100) and when C was 1, it would average C and D, instead of just c. As D would have been blank it averaged C and 0. Is there a way for the formula to ignore blanks? Thanks again. -- Thanks! Stephen "Ron Coderre" wrote: Try this: =SUMPRODUCT(SUMIF(B1:B100,{1,2},C1:C100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2})) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Thank you, I did get it to work. How would I do it if I want to average the percentages if either 1 or 2 were in the b column? Thanks in advance. -- Thanks! Stephen "Ron Coderre" wrote: Try this: =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1) Does that help? *********** Regards, Ron XL2002, WinXP "Stephen" wrote: Hi. I am trying to average a set of numbers but only if the number in the column to the left is equal to 1. For example: b1:b100 I have either the number 1, 2, 3, or 4, c1:c100 I have percentages I want to average only the percentages that have a 1 to the left of it in the b column. Thanks in advance... -- Thanks! Stephen |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| rolling average | Excel Worksheet Functions | |||
| plotted Average | Charts and Charting in Excel | |||
| What is this kind of average called? | Excel Worksheet Functions | |||
| Average Formula with Criteria | Excel Discussion (Misc queries) | |||
| how do I find an average number of specific words in a column | New Users to Excel | |||
Linear Mode
