| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average if
I am trying to create an average of a range of cells - B2:F2000 - but only if
Column CJ is 1. I am also trying to inset this equation to a different worksheet. The sheet with the data in it is called paste_data. Here is the equation I am trying to use but it results in an error. =AVERAGE(IF(paste_data!CJ2:CJ2000=1,paste_data!B2: F2000)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average if
There's nothing wrong with the formula. Did you enter it as an array? What
specific error do you get? -- Biff Microsoft Excel MVP "mparker" wrote in message ... I am trying to create an average of a range of cells - B2:F2000 - but only if Column CJ is 1. I am also trying to inset this equation to a different worksheet. The sheet with the data in it is called paste_data. Here is the equation I am trying to use but it results in an error. =AVERAGE(IF(paste_data!CJ2:CJ2000=1,paste_data!B2: F2000)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average if
Biff,
I get 0 as a result when entered as an array formula. However, this 'meat and potatoes' version seems to work... =IF(AND(MAX(paste_data!CJ2:CJ2000) =1,MIN(paste_data!CJ2:CJ2000)=1,COUNTBLANK(paste_d ata!CJ2:CJ2000)=0),AVERAGE(B2:F2000),"Don't do it") -- Jim Cone Portland, Oregon USA "T. Valko" wrote in message There's nothing wrong with the formula. Did you enter it as an array? What specific error do you get? -- Biff Microsoft Excel MVP "mparker" wrote in message I am trying to create an average of a range of cells - B2:F2000 - but only if Column CJ is 1. I am also trying to inset this equation to a different worksheet. The sheet with the data in it is called paste_data. Here is the equation I am trying to use but it results in an error. =AVERAGE(IF(paste_data!CJ2:CJ2000=1,paste_data!B2: F2000)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average if
=AVERAGE(IF(Sheet1!CJ2:CJ2000=1,Sheet1!B2:F2000))
Array entered, worked just fine for me. Confirmation formulas also verified the result was correct. -- Biff Microsoft Excel MVP "Jim Cone" wrote in message ... Biff, I get 0 as a result when entered as an array formula. However, this 'meat and potatoes' version seems to work... =IF(AND(MAX(paste_data!CJ2:CJ2000) =1,MIN(paste_data!CJ2:CJ2000)=1,COUNTBLANK(paste_d ata!CJ2:CJ2000)=0),AVERAGE(B2:F2000),"Don't do it") -- Jim Cone Portland, Oregon USA "T. Valko" wrote in message There's nothing wrong with the formula. Did you enter it as an array? What specific error do you get? -- Biff Microsoft Excel MVP "mparker" wrote in message I am trying to create an average of a range of cells - B2:F2000 - but only if Column CJ is 1. I am also trying to inset this equation to a different worksheet. The sheet with the data in it is called paste_data. Here is the equation I am trying to use but it results in an error. =AVERAGE(IF(paste_data!CJ2:CJ2000=1,paste_data!B2: F2000)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average if
Thanks the array is what I was missing.
"T. Valko" wrote: There's nothing wrong with the formula. Did you enter it as an array? What specific error do you get? -- Biff Microsoft Excel MVP "mparker" wrote in message ... I am trying to create an average of a range of cells - B2:F2000 - but only if Column CJ is 1. I am also trying to inset this equation to a different worksheet. The sheet with the data in it is called paste_data. Here is the equation I am trying to use but it results in an error. =AVERAGE(IF(paste_data!CJ2:CJ2000=1,paste_data!B2: F2000)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average if
Just one more questions, this equation seems to be calculating blanks as 0s
in the average. Any idea? "T. Valko" wrote: There's nothing wrong with the formula. Did you enter it as an array? What specific error do you get? -- Biff Microsoft Excel MVP "mparker" wrote in message ... I am trying to create an average of a range of cells - B2:F2000 - but only if Column CJ is 1. I am also trying to inset this equation to a different worksheet. The sheet with the data in it is called paste_data. Here is the equation I am trying to use but it results in an error. =AVERAGE(IF(paste_data!CJ2:CJ2000=1,paste_data!B2: F2000)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average if
=AVERAGE(IF((paste_data!CJ2:CJ2000=1)*(paste_data! B2:F2000<""),paste_data!B2:F2000))
"mparker" wrote: Just one more questions, this equation seems to be calculating blanks as 0s in the average. Any idea? "T. Valko" wrote: There's nothing wrong with the formula. Did you enter it as an array? What specific error do you get? -- Biff Microsoft Excel MVP "mparker" wrote in message ... I am trying to create an average of a range of cells - B2:F2000 - but only if Column CJ is 1. I am also trying to inset this equation to a different worksheet. The sheet with the data in it is called paste_data. Here is the equation I am trying to use but it results in an error. =AVERAGE(IF(paste_data!CJ2:CJ2000=1,paste_data!B2: F2000)) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average if
Biff,
It appears that the elephant I saw was not the elephant you saw... I saw the issue as: only average the data in B2:F2000 if every cell in CJ2:CJ2000 has a 1 in it. -- Jim Cone Portland, Oregon USA "T. Valko" wrote in message =AVERAGE(IF(Sheet1!CJ2:CJ2000=1,Sheet1!B2:F2000)) Array entered, worked just fine for me. Confirmation formulas also verified the result was correct. -- Biff Microsoft Excel MVP "Jim Cone" wrote in message ... Biff, I get 0 as a result when entered as an array formula. However, this 'meat and potatoes' version seems to work... =IF(AND(MAX(paste_data!CJ2:CJ2000) =1,MIN(paste_data!CJ2:CJ2000)=1,COUNTBLANK(paste_d ata!CJ2:CJ2000)=0),AVERAGE(B2:F2000),"Don't do it") -- Jim Cone Portland, Oregon USA |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average if
Anyway to change the B2 to C2 instead of changing it to B3?
"Idoia" wrote: =AVERAGE(IF((paste_data!CJ2:CJ2000=1)*(paste_data! B2:F2000<""),paste_data!B2:F2000)) "mparker" wrote: Just one more questions, this equation seems to be calculating blanks as 0s in the average. Any idea? "T. Valko" wrote: There's nothing wrong with the formula. Did you enter it as an array? What specific error do you get? -- Biff Microsoft Excel MVP "mparker" wrote in message ... I am trying to create an average of a range of cells - B2:F2000 - but only if Column CJ is 1. I am also trying to inset this equation to a different worksheet. The sheet with the data in it is called paste_data. Here is the equation I am trying to use but it results in an error. =AVERAGE(IF(paste_data!CJ2:CJ2000=1,paste_data!B2: F2000)) |
| 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
