Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question
For this formula =IF(COUNTIF(A2:A19;"<2")=0;0;AVERAGE(IF(A2:A19<2;A BS(B2:B19-C2:C19)))) How would I modify so it does the calculation only when A2:A19 is between 2 and 5 (including 2) ? Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question
"carl" wrote:
For this formula =IF(COUNTIF(A2:A19;"<2")=0;0;AVERAGE(IF(A2:A19<2;A BS(B2:B19-C2:C19)))) How would I modify so it does the calculation only when A2:A19 is between 2 and 5 (including 2) ? Please keep all related questions in the same thread. Posting anew causes confusion, duplicate and possible misdirection since some readers might not see all responses. I answered your question, even with the embellishment "including 2", in your original thread. I wrote.... "carl" wrote: For this formula =IF(COUNTIF(A2:A19,"=2")=0,0, AVERAGE(IF(A2:A19=2,ABS(B2:B19-C2:C19)))) How would I modify so it does the calculation only when A2:A19 is between 2 and 5 ? First, for XL2007 and later, the original formula could simplified as follows: =IFERROR(AVERAGE(IF(A2:A19=2,ABS(B2:B19-C2:C19))),0) And for your follow-up question: =IFERROR(AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2 :B19-C2:C19))),0) The tests "between" inclusively, including 2 and 5. Change "<=" and/or "<=" appropriately if you do not mean to include one or the other end point or both. The multipication behaves like a pairwise "AND" in this context. We cannot write AND(2<=A2:A19,A2:A19<=5) because the AND() function interprets the arrays, not the IF() function. In XL2003 and earlier, we can write: =IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0, AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19)))) That, too, tests "between" inclusively despite appearances due to the "<2". To exclude 2, use "<=2". To exclude 5, use "<5". A little tricky. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question
On Jan 31, 12:53*pm, "joeu2004" wrote:
"carl" wrote: For this formula =IF(COUNTIF(A2:A19;"<2")=0;0;AVERAGE(IF(A2:A19<2;A BS(B2:B19-C2:C19)))) How would I modify so it does the calculation only when A2:A19 is between 2 and 5 *(including 2) ? Please keep all related questions in the same thread. *Posting anew causes confusion, duplicate and possible misdirection since some readers might not see all responses. I answered your question, even with the embellishment "including 2", in your original thread. I wrote.... "carl" wrote: For this formula =IF(COUNTIF(A2:A19,"=2")=0,0, AVERAGE(IF(A2:A19=2,ABS(B2:B19-C2:C19)))) How would I modify so it does the calculation only when A2:A19 is between 2 and 5 ? First, for XL2007 and later, the original formula could simplified as follows: =IFERROR(AVERAGE(IF(A2:A19=2,ABS(B2:B19-C2:C19))),0) And for your follow-up question: =IFERROR(AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2 :B19-C2:C19))),0) The tests "between" inclusively, including 2 and 5. *Change "<=" and/or "<=" appropriately if you do not mean to include one or the other end point or both. The multipication behaves like a pairwise "AND" in this context. *We cannot write AND(2<=A2:A19,A2:A19<=5) because the AND() function interprets the arrays, not the IF() function. In XL2003 and earlier, we can write: =IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0, AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19)))) That, too, tests "between" inclusively despite appearances due to the "<2". To exclude 2, use "<=2". *To exclude 5, use "<5". *A little tricky. Thanks again and sorry about the reposting. I'm having some trouble following the example: In XL2003 and earlier, we can write: =IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0, AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19)))) That, too, tests "between" inclusively despite appearances due to the "<2". To exclude 2, use "<=2". To exclude 5, use "<5". A little tricky. So if I want the formula to include 2 but exclude 5 how would it look ? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question
"carl" wrote:
I'm having some trouble following the example: In XL2003 and earlier, we can write: =IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0, AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19)))) That, too, tests "between" inclusively despite appearances due to the "<2". To exclude 2, use "<=2". To exclude 5, use "<5". A little tricky. Sorry. My comments starting with "To exclude" were forcused on the COUNTIF parts. I a.s.s-u-me-d the rest would be "obvious". carl wrote: So if I want the formula to include 2 but exclude 5 how would it look ? =IF(COUNTIF(A2:A19,"<5")-COUNTIF(A2:A19,"<2")=0,0, AVERAGE(IF((2<=A2:A19)*(A2:A19<5),ABS(B2:B19-C2:C19)))) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question
On Jan 31, 1:32*pm, "joeu2004" wrote:
"carl" wrote: I'm having some trouble following the example: In XL2003 and earlier, we can write: =IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0, *AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19)))) That, too, tests "between" inclusively despite appearances due to the "<2". To exclude 2, use "<=2". *To exclude 5, use "<5". *A little tricky. Sorry. *My comments starting with "To exclude" were forcused on the COUNTIF parts. *I a.s.s-u-me-d the rest would be "obvious". carl wrote: So if I want the formula to include 2 but exclude 5 how would it look ? =IF(COUNTIF(A2:A19,"<5")-COUNTIF(A2:A19,"<2")=0,0, *AVERAGE(IF((2<=A2:A19)*(A2:A19<5),ABS(B2:B19-C2:C19)))) Thank you. This makes sense now and works well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie Formula Question - how to get formula to repeat in each subsequent row? | New Users to Excel | |||
Formula question | Excel Discussion (Misc queries) | |||
Formula Question | Excel Discussion (Misc queries) | |||
Another formula question | Excel Worksheet Functions | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) |