| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find every 2 col's: SUMPRODUCT, AVERAGE..
Hi, I have an example for sumproduct, finding product for each 2 lines in an
array. for sumproduct, have example that works. if someone can tell what double negs: "--" are for / how works with divisor / sometimes use "=" instead of "".. so can figure them out.. thanks example sumproduct that works: =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) PROBLEM: Is there a way to find the average of just the 1st column, to the rest of the 1st columns, trying (guesse gets average of all columns?): =IF(AC49="","",AVERAGE(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find every 2 col's: SUMPRODUCT, AVERAGE..
ps: - meant to say columns, not rows;
- using sumproduct, used "" sign, used: "=" sign. - array is AO:BH "nastech" wrote: Hi, I have an example for sumproduct, finding product for each 2 lines in an array. for sumproduct, have example that works. if someone can tell what double negs: "--" are for / how works with divisor / sometimes use "=" instead of "".. so can figure them out.. thanks example sumproduct that works: =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) PROBLEM: Is there a way to find the average of just the 1st column, to the rest of the 1st columns, trying (guesse gets average of all columns?): =IF(AC49="","",AVERAGE(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find every 2 col's: SUMPRODUCT, AVERAGE..
=IF(AC49="","",AVERAGE(IF(MOD(COLUMN($AP49:$BH49), 2)MOD(COLUMN($AP49),2),$AP49:$BH49)))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... Hi, I have an example for sumproduct, finding product for each 2 lines in an array. for sumproduct, have example that works. if someone can tell what double negs: "--" are for / how works with divisor / sometimes use "=" instead of "".. so can figure them out.. thanks example sumproduct that works: =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) PROBLEM: Is there a way to find the average of just the 1st column, to the rest of the 1st columns, trying (guesse gets average of all columns?): =IF(AC49="","",AVERAGE(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find every 2 col's: SUMPRODUCT, AVERAGE..
hi, tried that, did not seem to work, got a zero result: am trying to
Average every-other column only.. where array starts with AO to BH (not sure if that matters), but e.g. numbers colums 1 2 3 4: ..0599 4000 ..0539 53500 want to Average: .0599 & .0539, and: 4000 & 53500 separately. thanks. "Bob Phillips" wrote: =IF(AC49="","",AVERAGE(IF(MOD(COLUMN($AP49:$BH49), 2)MOD(COLUMN($AP49),2),$AP49:$BH49))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... Hi, I have an example for sumproduct, finding product for each 2 lines in an array. for sumproduct, have example that works. if someone can tell what double negs: "--" are for / how works with divisor / sometimes use "=" instead of "".. so can figure them out.. thanks example sumproduct that works: =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) PROBLEM: Is there a way to find the average of just the 1st column, to the rest of the 1st columns, trying (guesse gets average of all columns?): =IF(AC49="","",AVERAGE(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find every 2 col's: SUMPRODUCT, AVERAGE..
It workls for me, but if it should start at AO then use
=IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... hi, tried that, did not seem to work, got a zero result: am trying to Average every-other column only.. where array starts with AO to BH (not sure if that matters), but e.g. numbers colums 1 2 3 4: .0599 4000 .0539 53500 want to Average: .0599 & .0539, and: 4000 & 53500 separately. thanks. "Bob Phillips" wrote: =IF(AC49="","",AVERAGE(IF(MOD(COLUMN($AP49:$BH49), 2)MOD(COLUMN($AP49),2),$AP49:$BH49))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... Hi, I have an example for sumproduct, finding product for each 2 lines in an array. for sumproduct, have example that works. if someone can tell what double negs: "--" are for / how works with divisor / sometimes use "=" instead of "".. so can figure them out.. thanks example sumproduct that works: =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) PROBLEM: Is there a way to find the average of just the 1st column, to the rest of the 1st columns, trying (guesse gets average of all columns?): =IF(AC49="","",AVERAGE(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find every 2 col's: SUMPRODUCT, AVERAGE..
really? for what was saying at first, not too familiar with formula; but
average of .0599 & .056 = .0550 I am getting numbers in thousands, where 2nd columns a 4000 & 700 = 2350 average of all 4 would be: 1175, answer getting in thousands, trying to isolate the separate rows. where columns are between AO & BH, some of my examples that work start from either AO or AN. not sure how to set your example up then, tried couple. others that work a (range: AO:BH) =IF(N49="","",SUMPRODUCT(--(MOD(COLUMN($AO49:$BG49),2)=MOD(COLUMN($AO49),2)), $AO49:$BG49,$AP49:$BH49)-AM49) =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) range: AB:AK (seem to work, not 100% sure if correct) =IF(AC49="","",SUM(--(MOD(COLUMN($AC49:$AK49),2)MOD(COLUMN($AC49),2)), $AC49:$AK49)) =IF(AC49="","",SUMPRODUCT(--(MOD(COLUMN($AB49:$AJ49),2)=MOD(COLUMN($AB49),2)), $AB49:$AJ49,$AC49:$AK49)) "Bob Phillips" wrote: It workls for me, but if it should start at AO then use =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) "Bob Phillips" wrote: It workls for me, but if it should start at AO then use =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... hi, tried that, did not seem to work, got a zero result: am trying to Average every-other column only.. where array starts with AO to BH (not sure if that matters), but e.g. numbers colums 1 2 3 4: .0599 4000 .0539 53500 want to Average: .0599 & .0539, and: 4000 & 53500 separately. thanks. "Bob Phillips" wrote: =IF(AC49="","",AVERAGE(IF(MOD(COLUMN($AP49:$BH49), 2)MOD(COLUMN($AP49),2),$AP49:$BH49))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... Hi, I have an example for sumproduct, finding product for each 2 lines in an array. for sumproduct, have example that works. if someone can tell what double negs: "--" are for / how works with divisor / sometimes use "=" instead of "".. so can figure them out.. thanks example sumproduct that works: =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) PROBLEM: Is there a way to find the average of just the 1st column, to the rest of the 1st columns, trying (guesse gets average of all columns?): =IF(AC49="","",AVERAGE(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find every 2 col's: SUMPRODUCT, AVERAGE..
Is this what you are after
=IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=0)*($AO49:$BH49<""),$AO49:$BH49))) and =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=1)*($AO49:$BH49<""),$AO49:$BH49))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... really? for what was saying at first, not too familiar with formula; but average of .0599 & .056 = .0550 I am getting numbers in thousands, where 2nd columns a 4000 & 700 = 2350 average of all 4 would be: 1175, answer getting in thousands, trying to isolate the separate rows. where columns are between AO & BH, some of my examples that work start from either AO or AN. not sure how to set your example up then, tried couple. others that work a (range: AO:BH) =IF(N49="","",SUMPRODUCT(--(MOD(COLUMN($AO49:$BG49),2)=MOD(COLUMN($AO49),2)), $AO49:$BG49,$AP49:$BH49)-AM49) =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) range: AB:AK (seem to work, not 100% sure if correct) =IF(AC49="","",SUM(--(MOD(COLUMN($AC49:$AK49),2)MOD(COLUMN($AC49),2)), $AC49:$AK49)) =IF(AC49="","",SUMPRODUCT(--(MOD(COLUMN($AB49:$AJ49),2)=MOD(COLUMN($AB49),2)), $AB49:$AJ49,$AC49:$AK49)) "Bob Phillips" wrote: It workls for me, but if it should start at AO then use =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) "Bob Phillips" wrote: It workls for me, but if it should start at AO then use =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... hi, tried that, did not seem to work, got a zero result: am trying to Average every-other column only.. where array starts with AO to BH (not sure if that matters), but e.g. numbers colums 1 2 3 4: .0599 4000 .0539 53500 want to Average: .0599 & .0539, and: 4000 & 53500 separately. thanks. "Bob Phillips" wrote: =IF(AC49="","",AVERAGE(IF(MOD(COLUMN($AP49:$BH49), 2)MOD(COLUMN($AP49),2),$AP49:$BH49))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... Hi, I have an example for sumproduct, finding product for each 2 lines in an array. for sumproduct, have example that works. if someone can tell what double negs: "--" are for / how works with divisor / sometimes use "=" instead of "".. so can figure them out.. thanks example sumproduct that works: =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) PROBLEM: Is there a way to find the average of just the 1st column, to the rest of the 1st columns, trying (guesse gets average of all columns?): =IF(AC49="","",AVERAGE(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49)) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find every 2 col's: SUMPRODUCT, AVERAGE..
hi, thanks for the reply; trying to figure out how this type formula works
so do by self, can only get the feel the first time thru I guesse. examples now have a =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=0)*($AO49:$BH49<""),$AO49:$BH49))) =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) not sure, guessing one might be bit more dynamic than other for using same formula different areas, as other examples do; but error get here with both is: (VALUE!<"") when looking at calc steps, thanks. not sure if issue yet, can fix easy enought for blank lines, but given not all cells are filled in, is not a problem still here is it? thanks "Bob Phillips" wrote: Is this what you are after =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=0)*($AO49:$BH49<""),$AO49:$BH49))) and =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=1)*($AO49:$BH49<""),$AO49:$BH49))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... really? for what was saying at first, not too familiar with formula; but average of .0599 & .056 = .0550 I am getting numbers in thousands, where 2nd columns a 4000 & 700 = 2350 average of all 4 would be: 1175, answer getting in thousands, trying to isolate the separate rows. where columns are between AO & BH, some of my examples that work start from either AO or AN. not sure how to set your example up then, tried couple. others that work a (range: AO:BH) =IF(N49="","",SUMPRODUCT(--(MOD(COLUMN($AO49:$BG49),2)=MOD(COLUMN($AO49),2)), $AO49:$BG49,$AP49:$BH49)-AM49) =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) range: AB:AK (seem to work, not 100% sure if correct) =IF(AC49="","",SUM(--(MOD(COLUMN($AC49:$AK49),2)MOD(COLUMN($AC49),2)), $AC49:$AK49)) =IF(AC49="","",SUMPRODUCT(--(MOD(COLUMN($AB49:$AJ49),2)=MOD(COLUMN($AB49),2)), $AB49:$AJ49,$AC49:$AK49)) "Bob Phillips" wrote: It workls for me, but if it should start at AO then use =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) "Bob Phillips" wrote: It workls for me, but if it should start at AO then use =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... hi, tried that, did not seem to work, got a zero result: am trying to Average every-other column only.. where array starts with AO to BH (not sure if that matters), but e.g. numbers colums 1 2 3 4: .0599 4000 .0539 53500 want to Average: .0599 & .0539, and: 4000 & 53500 separately. thanks. "Bob Phillips" wrote: =IF(AC49="","",AVERAGE(IF(MOD(COLUMN($AP49:$BH49), 2)MOD(COLUMN($AP49),2),$AP49:$BH49))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... Hi, I have an example for sumproduct, finding product for each 2 lines in an array. for sumproduct, have example that works. if someone can tell what double negs: "--" are for / how works with divisor / sometimes use "=" instead of "".. so can figure them out.. thanks example sumproduct that works: =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) PROBLEM: Is there a way to find the average of just the 1st column, to the rest of the 1st columns, trying (guesse gets average of all columns?): =IF(AC49="","",AVERAGE(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49)) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find every 2 col's: SUMPRODUCT, AVERAGE..
They are very different, the first averages every other column using a
hard-coded number to test against, the second gets the column number using the mod function. This makes the second unnecessarily complex IMO. The ($AO49:$BH49<"") caters for empty cells. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... hi, thanks for the reply; trying to figure out how this type formula works so do by self, can only get the feel the first time thru I guesse. examples now have a =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=0)*($AO49:$BH49<""),$AO49:$BH49))) =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) not sure, guessing one might be bit more dynamic than other for using same formula different areas, as other examples do; but error get here with both is: (VALUE!<"") when looking at calc steps, thanks. not sure if issue yet, can fix easy enought for blank lines, but given not all cells are filled in, is not a problem still here is it? thanks "Bob Phillips" wrote: Is this what you are after =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=0)*($AO49:$BH49<""),$AO49:$BH49))) and =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=1)*($AO49:$BH49<""),$AO49:$BH49))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... really? for what was saying at first, not too familiar with formula; but average of .0599 & .056 = .0550 I am getting numbers in thousands, where 2nd columns a 4000 & 700 = 2350 average of all 4 would be: 1175, answer getting in thousands, trying to isolate the separate rows. where columns are between AO & BH, some of my examples that work start from either AO or AN. not sure how to set your example up then, tried couple. others that work a (range: AO:BH) =IF(N49="","",SUMPRODUCT(--(MOD(COLUMN($AO49:$BG49),2)=MOD(COLUMN($AO49),2)), $AO49:$BG49,$AP49:$BH49)-AM49) =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) range: AB:AK (seem to work, not 100% sure if correct) =IF(AC49="","",SUM(--(MOD(COLUMN($AC49:$AK49),2)MOD(COLUMN($AC49),2)), $AC49:$AK49)) =IF(AC49="","",SUMPRODUCT(--(MOD(COLUMN($AB49:$AJ49),2)=MOD(COLUMN($AB49),2)), $AB49:$AJ49,$AC49:$AK49)) "Bob Phillips" wrote: It workls for me, but if it should start at AO then use =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) "Bob Phillips" wrote: It workls for me, but if it should start at AO then use =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... hi, tried that, did not seem to work, got a zero result: am trying to Average every-other column only.. where array starts with AO to BH (not sure if that matters), but e.g. numbers colums 1 2 3 4: .0599 4000 .0539 53500 want to Average: .0599 & .0539, and: 4000 & 53500 separately. thanks. "Bob Phillips" wrote: =IF(AC49="","",AVERAGE(IF(MOD(COLUMN($AP49:$BH49), 2)MOD(COLUMN($AP49),2),$AP49:$BH49))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... Hi, I have an example for sumproduct, finding product for each 2 lines in an array. for sumproduct, have example that works. if someone can tell what double negs: "--" are for / how works with divisor / sometimes use "=" instead of "".. so can figure them out.. thanks example sumproduct that works: =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) PROBLEM: Is there a way to find the average of just the 1st column, to the rest of the 1st columns, trying (guesse gets average of all columns?): =IF(AC49="","",AVERAGE(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49)) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find every 2 col's: SUMPRODUCT, AVERAGE..
hi, thanks, had too many things going, forgot to do cntrl-shift-enter.
thanks... "Bob Phillips" wrote: They are very different, the first averages every other column using a hard-coded number to test against, the second gets the column number using the mod function. This makes the second unnecessarily complex IMO. The ($AO49:$BH49<"") caters for empty cells. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... hi, thanks for the reply; trying to figure out how this type formula works so do by self, can only get the feel the first time thru I guesse. examples now have a =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=0)*($AO49:$BH49<""),$AO49:$BH49))) =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) not sure, guessing one might be bit more dynamic than other for using same formula different areas, as other examples do; but error get here with both is: (VALUE!<"") when looking at calc steps, thanks. not sure if issue yet, can fix easy enought for blank lines, but given not all cells are filled in, is not a problem still here is it? thanks "Bob Phillips" wrote: Is this what you are after =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=0)*($AO49:$BH49<""),$AO49:$BH49))) and =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=1)*($AO49:$BH49<""),$AO49:$BH49))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... really? for what was saying at first, not too familiar with formula; but average of .0599 & .056 = .0550 I am getting numbers in thousands, where 2nd columns a 4000 & 700 = 2350 average of all 4 would be: 1175, answer getting in thousands, trying to isolate the separate rows. where columns are between AO & BH, some of my examples that work start from either AO or AN. not sure how to set your example up then, tried couple. others that work a (range: AO:BH) =IF(N49="","",SUMPRODUCT(--(MOD(COLUMN($AO49:$BG49),2)=MOD(COLUMN($AO49),2)), $AO49:$BG49,$AP49:$BH49)-AM49) =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) range: AB:AK (seem to work, not 100% sure if correct) =IF(AC49="","",SUM(--(MOD(COLUMN($AC49:$AK49),2)MOD(COLUMN($AC49),2)), $AC49:$AK49)) =IF(AC49="","",SUMPRODUCT(--(MOD(COLUMN($AB49:$AJ49),2)=MOD(COLUMN($AB49),2)), $AB49:$AJ49,$AC49:$AK49)) "Bob Phillips" wrote: It workls for me, but if it should start at AO then use =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) "Bob Phillips" wrote: It workls for me, but if it should start at AO then use =IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... hi, tried that, did not seem to work, got a zero result: am trying to Average every-other column only.. where array starts with AO to BH (not sure if that matters), but e.g. numbers colums 1 2 3 4: .0599 4000 .0539 53500 want to Average: .0599 & .0539, and: 4000 & 53500 separately. thanks. "Bob Phillips" wrote: =IF(AC49="","",AVERAGE(IF(MOD(COLUMN($AP49:$BH49), 2)MOD(COLUMN($AP49),2),$AP49:$BH49))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nastech" wrote in message ... Hi, I have an example for sumproduct, finding product for each 2 lines in an array. for sumproduct, have example that works. if someone can tell what double negs: "--" are for / how works with divisor / sometimes use "=" instead of "".. so can figure them out.. thanks example sumproduct that works: =IF(AC49="","",IF(AP49="",0,SUM(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49))) PROBLEM: Is there a way to find the average of just the 1st column, to the rest of the 1st columns, trying (guesse gets average of all columns?): =IF(AC49="","",AVERAGE(--(MOD(COLUMN($AP49:$BH49),2)MOD(COLUMN($AP49),2)), $AP49:$BH49)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
| AVERAGE & SUMPRODUCT | Excel Discussion (Misc queries) | |||
| Using Sumproduct to Find Average | Excel Discussion (Misc queries) | |||
| Sumproduct Average | Excel Discussion (Misc queries) | |||
| sumproduct & average | Excel Worksheet Functions | |||
Linear Mode
