|
AverageIF
I have two questions.
1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,"2") this works great unless all values = 0 then I get #DIV/0!. I would like to have the value remain 0 (The greater than 2 is because we have a +;- 2 standard error in our insequel pull of information. How would I correct the error message? 2. When using AVERAGEIF for cells not in a range...actual line =AVERAGEIF(A1,A5,A6,"2") this formula is not acceptable at all. Any way to do this? -- Thank you for your time! John |
AverageIF
=IF(ISERROR(AVERAGEIF(A1:A5,"2")),0,AVERAGEIF(A1: A5,"2"))
-- Gary''s Student - gsnu201001 "Very Basic User" wrote: I have two questions. 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,"2") this works great unless all values = 0 then I get #DIV/0!. I would like to have the value remain 0 (The greater than 2 is because we have a +;- 2 standard error in our insequel pull of information. How would I correct the error message? 2. When using AVERAGEIF for cells not in a range...actual line =AVERAGEIF(A1,A5,A6,"2") this formula is not acceptable at all. Any way to do this? -- Thank you for your time! John |
AverageIF
Try these...
1: =IFERROR(AVERAGEIF(A1:A5,"2"),0) 2: no elegant way to do this one unless the non-contiguous cells follow a set pattern (every other cell, every 5th cell, every 10th cell, etc.) =SUM((A12)*A1,(A52)*A5,(A62)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2) With an error trap: =IFERROR(SUM((A12)*A1,(A52)*A5,(A62)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2),0) -- Biff Microsoft Excel MVP "Very Basic User" wrote in message ... I have two questions. 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,"2") this works great unless all values = 0 then I get #DIV/0!. I would like to have the value remain 0 (The greater than 2 is because we have a +;- 2 standard error in our insequel pull of information. How would I correct the error message? 2. When using AVERAGEIF for cells not in a range...actual line =AVERAGEIF(A1,A5,A6,"2") this formula is not acceptable at all. Any way to do this? -- Thank you for your time! John |
AverageIF
-- Thank you for your time! John "Gary''s Student" wrote: =IF(ISERROR(AVERAGEIF(A1:A5,"2")),0,AVERAGEIF(A1: A5,"2")) -- Gary''s Student - gsnu201001 "Very Basic User" wrote: I have two questions. 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,"2") this works great unless all values = 0 then I get #DIV/0!. I would like to have the value remain 0 (The greater than 2 is because we have a +;- 2 standard error in our insequel pull of information. How would I correct the error message? 2. When using AVERAGEIF for cells not in a range...actual line =AVERAGEIF(A1,A5,A6,"2") this formula is not acceptable at all. Any way to do this? -- Thank you for your time! John |
AverageIF
Gary's student your solution was perfect for my first question. Thank you!
-- Thank you for your time! John "Gary''s Student" wrote: =IF(ISERROR(AVERAGEIF(A1:A5,"2")),0,AVERAGEIF(A1: A5,"2")) -- Gary''s Student - gsnu201001 "Very Basic User" wrote: I have two questions. 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,"2") this works great unless all values = 0 then I get #DIV/0!. I would like to have the value remain 0 (The greater than 2 is because we have a +;- 2 standard error in our insequel pull of information. How would I correct the error message? 2. When using AVERAGEIF for cells not in a range...actual line =AVERAGEIF(A1,A5,A6,"2") this formula is not acceptable at all. Any way to do this? -- Thank you for your time! John |
AverageIF
T. Valko, thank you very much I was afraid of that. I actually started by
just transfering summed cells to another location, having the code read to those cells and then hiding the columns. This is a better way to keep it clean! thank you! -- Thank you for your time! John "T. Valko" wrote: Try these... 1: =IFERROR(AVERAGEIF(A1:A5,"2"),0) 2: no elegant way to do this one unless the non-contiguous cells follow a set pattern (every other cell, every 5th cell, every 10th cell, etc.) =SUM((A12)*A1,(A52)*A5,(A62)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2) With an error trap: =IFERROR(SUM((A12)*A1,(A52)*A5,(A62)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2),0) -- Biff Microsoft Excel MVP "Very Basic User" wrote in message ... I have two questions. 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,"2") this works great unless all values = 0 then I get #DIV/0!. I would like to have the value remain 0 (The greater than 2 is because we have a +;- 2 standard error in our insequel pull of information. How would I correct the error message? 2. When using AVERAGEIF for cells not in a range...actual line =AVERAGEIF(A1,A5,A6,"2") this formula is not acceptable at all. Any way to do this? -- Thank you for your time! John . |
AverageIF
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Very Basic User" wrote in message ... T. Valko, thank you very much I was afraid of that. I actually started by just transfering summed cells to another location, having the code read to those cells and then hiding the columns. This is a better way to keep it clean! thank you! -- Thank you for your time! John "T. Valko" wrote: Try these... 1: =IFERROR(AVERAGEIF(A1:A5,"2"),0) 2: no elegant way to do this one unless the non-contiguous cells follow a set pattern (every other cell, every 5th cell, every 10th cell, etc.) =SUM((A12)*A1,(A52)*A5,(A62)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2) With an error trap: =IFERROR(SUM((A12)*A1,(A52)*A5,(A62)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2),0) -- Biff Microsoft Excel MVP "Very Basic User" wrote in message ... I have two questions. 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,"2") this works great unless all values = 0 then I get #DIV/0!. I would like to have the value remain 0 (The greater than 2 is because we have a +;- 2 standard error in our insequel pull of information. How would I correct the error message? 2. When using AVERAGEIF for cells not in a range...actual line =AVERAGEIF(A1,A5,A6,"2") this formula is not acceptable at all. Any way to do this? -- Thank you for your time! John . |
| All times are GMT +1. The time now is 06:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com