| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find every 2 col's: SUMPRODUCT, AVERAGE.. | Excel Discussion (Misc queries) | |||
| 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 Worksheet Functions | |||
Linear Mode
