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

[Guest]

(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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

ouch, sorry missed that step, thanks.

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.