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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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))
 
ps: - meant to say columns, not rows;
- using sumproduct, used ">" sign, used: "=" sign.
- array is AO:BH
 
=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)
 
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.
 
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)
 
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 are:
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 are: (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))
 
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)
 
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 are
=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
 
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)
 
Back
Top