Find every 2 col's: SUMPRODUCT, AVERAGE..
 

(070815) Find every 2 col's: SUMPRODUCT, AVERAGE..

Hi, I have an example for sumproduct, finding product for each 2 columns 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))

- using sumproduct, used "" sign, sum used: "=" sign.
- array is AO:BH


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.

Find every 2 col's: SUMPRODUCT, AVERAGE..
 

With data starting in AO:


=IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49)))

..0599 & .0539 etc

and

=IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)<MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49)))

4000 & 53500 etc

"nastech" wrote:

(070815) Find every 2 col's: SUMPRODUCT, AVERAGE..

Hi, I have an example for sumproduct, finding product for each 2 columns 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))

- using sumproduct, used "" sign, sum used: "=" sign.
- array is AO:BH


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: .0599 & .0539 separately. thanks.

Find every 2 col's: SUMPRODUCT, AVERAGE..
 

hi, thanks for the reply. I gave that a try but saw same error in both cells:
(VALUE!<"")

not sure if you were one helped me before with this type of problem: double
column & range /

I had been trying to word them out to see the mehcanics of what is
happening, so can fix myself; curious what the double neg "--" does in
examples, and how the divisor is affecting formula as well. not sure what is
not working with this one either. thanks.



"Toppers" wrote:

With data starting in AO:


=IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49)))

.0599 & .0539 etc

and

=IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)<MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49)))

4000 & 53500 etc

"nastech" wrote:

(070815) Find every 2 col's: SUMPRODUCT, AVERAGE..

Hi, I have an example for sumproduct, finding product for each 2 columns 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))

- using sumproduct, used "" sign, sum used: "=" sign.
- array is AO:BH


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: .0599 & .0539 separately. thanks.

Find every 2 col's: SUMPRODUCT, AVERAGE..
 

Worked fine more me (as did the previous posting by Bob Phillips) BUT what I
didn't say was enter with Ctrl+Shift+Enter!

the --- converts a TRUE/FALSE to 1/0.



"nastech" wrote:

hi, thanks for the reply. I gave that a try but saw same error in both cells:
(VALUE!<"")

not sure if you were one helped me before with this type of problem: double
column & range /

I had been trying to word them out to see the mehcanics of what is
happening, so can fix myself; curious what the double neg "--" does in
examples, and how the divisor is affecting formula as well. not sure what is
not working with this one either. thanks.



"Toppers" wrote:

With data starting in AO:


=IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49)))

.0599 & .0539 etc

and

=IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)<MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49)))

4000 & 53500 etc

"nastech" wrote:

(070815) Find every 2 col's: SUMPRODUCT, AVERAGE..

Hi, I have an example for sumproduct, finding product for each 2 columns 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))

- using sumproduct, used "" sign, sum used: "=" sign.
- array is AO:BH


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: .0599 & .0539 separately. thanks.

Find every 2 col's: SUMPRODUCT, AVERAGE..
 

ouch, sorry missed that step, thanks.

"Toppers" wrote:

Worked fine more me (as did the previous posting by Bob Phillips) BUT what I
didn't say was enter with Ctrl+Shift+Enter!

the --- converts a TRUE/FALSE to 1/0.



"nastech" wrote:

hi, thanks for the reply. I gave that a try but saw same error in both cells:
(VALUE!<"")

not sure if you were one helped me before with this type of problem: double
column & range /

I had been trying to word them out to see the mehcanics of what is
happening, so can fix myself; curious what the double neg "--" does in
examples, and how the divisor is affecting formula as well. not sure what is
not working with this one either. thanks.



"Toppers" wrote:

With data starting in AO:


=IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)=MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49)))

.0599 & .0539 etc

and

=IF(AC49="","",AVERAGE(IF((MOD(COLUMN($AO49:$BH49) ,2)<MOD(COLUMN($AO49),2))*($AO49:$BH49<""),$AO49: $BH49)))

4000 & 53500 etc

"nastech" wrote:

(070815) Find every 2 col's: SUMPRODUCT, AVERAGE..

Hi, I have an example for sumproduct, finding product for each 2 columns 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))

- using sumproduct, used "" sign, sum used: "=" sign.
- array is AO:BH


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: .0599 & .0539 separately. thanks.