| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDIRECT & R1C1 Ref style
Using Excel 2007;
The following formula sums every other column in a range. =SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1) How can I modify the formula to refer to a range from column A to 'one column left of the formula'? I've tried the following but the result is #REF! =SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1)) A named range works well but I'll copy this formula to hundreds of lines in multiple sheets and I'd rather not have to modify the range name for each sheet. Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using INDIRECT & R1C1 Ref style
Try it like this:
=SUMPRODUCT(--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0),INDIRECT("RC1:RC[-1]",0)) -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Using Excel 2007; The following formula sums every other column in a range. =SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1) How can I modify the formula to refer to a range from column A to 'one column left of the formula'? I've tried the following but the result is #REF! =SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1)) A named range works well but I'll copy this formula to hundreds of lines in multiple sheets and I'd rather not have to modify the range name for each sheet. Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using INDIRECT & R1C1 Ref style
Thank you.
This works great. Can you tell me how the double dash operates? Dave "T. Valko" wrote: Try it like this: =SUMPRODUCT(--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0),INDIRECT("RC1:RC[-1]",0)) -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Using Excel 2007; The following formula sums every other column in a range. =SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1) How can I modify the formula to refer to a range from column A to 'one column left of the formula'? I've tried the following but the result is #REF! =SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1)) A named range works well but I'll copy this formula to hundreds of lines in multiple sheets and I'd rather not have to modify the range name for each sheet. Thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using INDIRECT & R1C1 Ref style
MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0
That expression will return an array of either TRUE or FALSE. The double unary coerces those into numeric values 1 and 0. TRUE = 1, FALSE = 0. --(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0) If the range was A1:D1... 5...7...3...5 Then: MOD(COLUMN(A1:D1),2)=0 evaluates to: {F,T,F,T} The double unary coerces these to numerics: {0,1,0,1} Then the 2 arrays are multiplied together: {0,1,0,1} * {5,7,3,5} 0*5=0 1*7=7 0*3=0 1*5=5 Then SUMPRODUCT sums up the result: SUMPRODUCT({0,7,0,5}) =12 See this for more info on SUMPRODUCT: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Thank you. This works great. Can you tell me how the double dash operates? Dave "T. Valko" wrote: Try it like this: =SUMPRODUCT(--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0),INDIRECT("RC1:RC[-1]",0)) -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Using Excel 2007; The following formula sums every other column in a range. =SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1) How can I modify the formula to refer to a range from column A to 'one column left of the formula'? I've tried the following but the result is #REF! =SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1)) A named range works well but I'll copy this formula to hundreds of lines in multiple sheets and I'd rather not have to modify the range name for each sheet. Thank you. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using INDIRECT & R1C1 Ref style
Thanks T.
I read an explanation at http://www.xldynamic.com/source/xld....T.html#explain but was still perplexed. Your explanation was far superior. "T. Valko" wrote: MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0 That expression will return an array of either TRUE or FALSE. The double unary coerces those into numeric values 1 and 0. TRUE = 1, FALSE = 0. --(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0) If the range was A1:D1... 5...7...3...5 Then: MOD(COLUMN(A1:D1),2)=0 evaluates to: {F,T,F,T} The double unary coerces these to numerics: {0,1,0,1} Then the 2 arrays are multiplied together: {0,1,0,1} * {5,7,3,5} 0*5=0 1*7=7 0*3=0 1*5=5 Then SUMPRODUCT sums up the result: SUMPRODUCT({0,7,0,5}) =12 See this for more info on SUMPRODUCT: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Thank you. This works great. Can you tell me how the double dash operates? Dave "T. Valko" wrote: Try it like this: =SUMPRODUCT(--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0),INDIRECT("RC1:RC[-1]",0)) -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Using Excel 2007; The following formula sums every other column in a range. =SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1) How can I modify the formula to refer to a range from column A to 'one column left of the formula'? I've tried the following but the result is #REF! =SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1)) A named range works well but I'll copy this formula to hundreds of lines in multiple sheets and I'd rather not have to modify the range name for each sheet. Thank you. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using INDIRECT & R1C1 Ref style
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Thanks T. I read an explanation at http://www.xldynamic.com/source/xld....T.html#explain but was still perplexed. Your explanation was far superior. "T. Valko" wrote: MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0 That expression will return an array of either TRUE or FALSE. The double unary coerces those into numeric values 1 and 0. TRUE = 1, FALSE = 0. --(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0) If the range was A1:D1... 5...7...3...5 Then: MOD(COLUMN(A1:D1),2)=0 evaluates to: {F,T,F,T} The double unary coerces these to numerics: {0,1,0,1} Then the 2 arrays are multiplied together: {0,1,0,1} * {5,7,3,5} 0*5=0 1*7=7 0*3=0 1*5=5 Then SUMPRODUCT sums up the result: SUMPRODUCT({0,7,0,5}) =12 See this for more info on SUMPRODUCT: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Thank you. This works great. Can you tell me how the double dash operates? Dave "T. Valko" wrote: Try it like this: =SUMPRODUCT(--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0),INDIRECT("RC1:RC[-1]",0)) -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Using Excel 2007; The following formula sums every other column in a range. =SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1) How can I modify the formula to refer to a range from column A to 'one column left of the formula'? I've tried the following but the result is #REF! =SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1)) A named range works well but I'll copy this formula to hundreds of lines in multiple sheets and I'd rather not have to modify the range name for each sheet. Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum Indirect Using R1C1 Style | Excel Worksheet Functions | |||
| R1C1 reference style | Excel Discussion (Misc queries) | |||
| How do I change sheet notation from R1C1 style to A1 style in XL 2 | Setting up and Configuration of Excel | |||
| can a1 reference style and r1c1 style be used in same formula? | Excel Worksheet Functions | |||
| R1C1 reference style | Excel Discussion (Misc queries) | |||
Linear Mode
