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)) |
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 |