| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGE (But only IF)
I have 4 named Columns, for simplicity they a Code, Red, Blue and Green.
In the Code Column are the numbers 1,2, or 3 and are random. The Red, Blue and Green Columns contain numbers anywhere between 1 and 100. But, not all the rows in the coloured columns contain numbers. I am trying to find the Average of each of the coloured Columns but only where I specific a code. I started with the following, =SUMIF(Code,1,Red)/COUNTIF(Code,1) but found that the sum was being divided by the total of the Code 1's and not the number of cells that contain data. Unfortunately, I have tried all sorts of other combinations but with my level of knowledge, have got no further. I would be grateful for some guidance. -- Glenn |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AVERAGE (But only IF)
Try this array* formula:
=AVERAGE(IF(Code=1,Red)) * As this is an array formula, then once you have typed it in (or subsequently amend it), you should use CTRL-SHIFT-ENTER to commit it rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you should not type these yourself. The named ranges should all have the same number of cells. Hope this helps. Pete On Feb 7, 6:30*pm, Brampton76 wrote: I have 4 named Columns, for simplicity they a Code, Red, Blue and Green.. * In the Code Column are the numbers 1,2, or 3 and are random. *The Red, Blue and Green Columns contain numbers anywhere between 1 and 100. *But, not all the rows in the coloured columns contain numbers. *I am trying to find the Average of each of the coloured Columns but only where I specific a code. *I started with the following, =SUMIF(Code,1,Red)/COUNTIF(Code,1) but found that the sum was being divided by the total of the Code 1's and not the number of cells that contain data. *Unfortunately, I have tried all sorts of other combinations but with my level of knowledge, have got no further. *I would be grateful for some guidance. -- Glenn |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AVERAGE (But only IF)
Try this array formula** :
=AVERAGE(IF((Code=1)*(Red<""),Red)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Brampton76" wrote in message ... I have 4 named Columns, for simplicity they a Code, Red, Blue and Green. In the Code Column are the numbers 1,2, or 3 and are random. The Red, Blue and Green Columns contain numbers anywhere between 1 and 100. But, not all the rows in the coloured columns contain numbers. I am trying to find the Average of each of the coloured Columns but only where I specific a code. I started with the following, =SUMIF(Code,1,Red)/COUNTIF(Code,1) but found that the sum was being divided by the total of the Code 1's and not the number of cells that contain data. Unfortunately, I have tried all sorts of other combinations but with my level of knowledge, have got no further. I would be grateful for some guidance. -- Glenn |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AVERAGE (But only IF)
Many thanks. I did find though, that my named columns had to be of the same
length as the data ie, I seem unable to name the whole column and just drop the data in. I simply received a #NUM! error each time. That said, a little bit of tweaking and I have still saved a fair bit of time using your formula. Many thanks for everyones help. -- Glenn "T. Valko" wrote: Try this array formula** : =AVERAGE(IF((Code=1)*(Red<""),Red)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Brampton76" wrote in message ... I have 4 named Columns, for simplicity they a Code, Red, Blue and Green. In the Code Column are the numbers 1,2, or 3 and are random. The Red, Blue and Green Columns contain numbers anywhere between 1 and 100. But, not all the rows in the coloured columns contain numbers. I am trying to find the Average of each of the coloured Columns but only where I specific a code. I started with the following, =SUMIF(Code,1,Red)/COUNTIF(Code,1) but found that the sum was being divided by the total of the Code 1's and not the number of cells that contain data. Unfortunately, I have tried all sorts of other combinations but with my level of knowledge, have got no further. I would be grateful for some guidance. -- Glenn |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AVERAGE (But only IF)
Maybe you should consider using dynamic ranges:
http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP "Brampton76" wrote in message ... Many thanks. I did find though, that my named columns had to be of the same length as the data ie, I seem unable to name the whole column and just drop the data in. I simply received a #NUM! error each time. That said, a little bit of tweaking and I have still saved a fair bit of time using your formula. Many thanks for everyones help. -- Glenn "T. Valko" wrote: Try this array formula** : =AVERAGE(IF((Code=1)*(Red<""),Red)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Brampton76" wrote in message ... I have 4 named Columns, for simplicity they a Code, Red, Blue and Green. In the Code Column are the numbers 1,2, or 3 and are random. The Red, Blue and Green Columns contain numbers anywhere between 1 and 100. But, not all the rows in the coloured columns contain numbers. I am trying to find the Average of each of the coloured Columns but only where I specific a code. I started with the following, =SUMIF(Code,1,Red)/COUNTIF(Code,1) but found that the sum was being divided by the total of the Code 1's and not the number of cells that contain data. Unfortunately, I have tried all sorts of other combinations but with my level of knowledge, have got no further. I would be grateful for some guidance. -- Glenn |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AVERAGE (But only IF)
Again, many thanks, and also for the website link. Lots to learn!
-- Glenn "T. Valko" wrote: Maybe you should consider using dynamic ranges: http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP "Brampton76" wrote in message ... Many thanks. I did find though, that my named columns had to be of the same length as the data ie, I seem unable to name the whole column and just drop the data in. I simply received a #NUM! error each time. That said, a little bit of tweaking and I have still saved a fair bit of time using your formula. Many thanks for everyones help. -- Glenn "T. Valko" wrote: Try this array formula** : =AVERAGE(IF((Code=1)*(Red<""),Red)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Brampton76" wrote in message ... I have 4 named Columns, for simplicity they a Code, Red, Blue and Green. In the Code Column are the numbers 1,2, or 3 and are random. The Red, Blue and Green Columns contain numbers anywhere between 1 and 100. But, not all the rows in the coloured columns contain numbers. I am trying to find the Average of each of the coloured Columns but only where I specific a code. I started with the following, =SUMIF(Code,1,Red)/COUNTIF(Code,1) but found that the sum was being divided by the total of the Code 1's and not the number of cells that contain data. Unfortunately, I have tried all sorts of other combinations but with my level of knowledge, have got no further. I would be grateful for some guidance. -- Glenn |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AVERAGE (But only IF)
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Brampton76" wrote in message ... Again, many thanks, and also for the website link. Lots to learn! -- Glenn "T. Valko" wrote: Maybe you should consider using dynamic ranges: http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP "Brampton76" wrote in message ... Many thanks. I did find though, that my named columns had to be of the same length as the data ie, I seem unable to name the whole column and just drop the data in. I simply received a #NUM! error each time. That said, a little bit of tweaking and I have still saved a fair bit of time using your formula. Many thanks for everyones help. -- Glenn "T. Valko" wrote: Try this array formula** : =AVERAGE(IF((Code=1)*(Red<""),Red)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Brampton76" wrote in message ... I have 4 named Columns, for simplicity they a Code, Red, Blue and Green. In the Code Column are the numbers 1,2, or 3 and are random. The Red, Blue and Green Columns contain numbers anywhere between 1 and 100. But, not all the rows in the coloured columns contain numbers. I am trying to find the Average of each of the coloured Columns but only where I specific a code. I started with the following, =SUMIF(Code,1,Red)/COUNTIF(Code,1) but found that the sum was being divided by the total of the Code 1's and not the number of cells that contain data. Unfortunately, I have tried all sorts of other combinations but with my level of knowledge, have got no further. I would be grateful for some guidance. -- Glenn |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
| Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
| how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions | |||
Linear Mode
