Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum every nth cell in a row
Sorry Posted in wrong group earlier
Hi I am trying to adapt Bob Phillips formula =SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32)-ROW(B2),n)+1=m)) I have cells, e5,e6,e7,e8,e9,e10,e11,e12,e13,e14,e15, etc I want to sum, e5,e9,e13 etc I will need to adapt this in another cell to add, e6,e10,e14 etc and therefore need to understand the N + M value I have entered the formula below in cell c5, but get 0. Have entered as array formula {=SUMPRODUCT((E5:AY5)*(MOD(ROW(E5:AY5)-ROW(B2),4)+1=F5))} What am I doing wrong Help appriciated Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200803/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum every nth cell in a row
need to understand the N + M value
N = divisor used in the MOD function M = mod For example: Using cell E5: MOD(ROW(E5),n)=m MOD(ROW(E5),4)=1 I want to sum, e5,e9,e13 etc =SUMPRODUCT(--(MOD(ROW(E5:E100),4)=1),E5:E100) I will need to adapt this in another cell to add, e6,e10,e14 etc =SUMPRODUCT(--(MOD(ROW(E5:E100),4)=2),E5:E100) -- Biff Microsoft Excel MVP "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:81808c2606b7d@uwe... Sorry Posted in wrong group earlier Hi I am trying to adapt Bob Phillips formula =SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32)-ROW(B2),n)+1=m)) I have cells, e5,e6,e7,e8,e9,e10,e11,e12,e13,e14,e15, etc I want to sum, e5,e9,e13 etc I will need to adapt this in another cell to add, e6,e10,e14 etc and therefore need to understand the N + M value I have entered the formula below in cell c5, but get 0. Have entered as array formula {=SUMPRODUCT((E5:AY5)*(MOD(ROW(E5:AY5)-ROW(B2),4)+1=F5))} What am I doing wrong Help appriciated Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200803/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum every nth cell in a row
Keep getting #Value! error
copied and pasted in c5, and entered with shift+ctl+ent =SUMPRODUCT(--(MOD(ROW(E5:E100),4)=1),E5:E100) Any ieas? T. Valko wrote: need to understand the N + M value N = divisor used in the MOD function M = mod For example: Using cell E5: MOD(ROW(E5),n)=m MOD(ROW(E5),4)=1 I want to sum, e5,e9,e13 etc =SUMPRODUCT(--(MOD(ROW(E5:E100),4)=1),E5:E100) I will need to adapt this in another cell to add, e6,e10,e14 etc =SUMPRODUCT(--(MOD(ROW(E5:E100),4)=2),E5:E100) Sorry Posted in wrong group earlier [quoted text clipped - 18 lines] Brian -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum every nth cell in a row
entered with shift+ctl+ent
Doesn't need to be array entered but that won't cause an error. Just ENTER will work. Keep getting #Value! error Are there any #VALUE! errors already in the range? Here's a small sample file that demonstrates this. xSumNth.xls 14kb http://cjoint.com/?dvxr6QVxsY -- Biff Microsoft Excel MVP "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:81816da88aaa2@uwe... Keep getting #Value! error copied and pasted in c5, and entered with shift+ctl+ent =SUMPRODUCT(--(MOD(ROW(E5:E100),4)=1),E5:E100) Any ieas? T. Valko wrote: need to understand the N + M value N = divisor used in the MOD function M = mod For example: Using cell E5: MOD(ROW(E5),n)=m MOD(ROW(E5),4)=1 I want to sum, e5,e9,e13 etc =SUMPRODUCT(--(MOD(ROW(E5:E100),4)=1),E5:E100) I will need to adapt this in another cell to add, e6,e10,e14 etc =SUMPRODUCT(--(MOD(ROW(E5:E100),4)=2),E5:E100) Sorry Posted in wrong group earlier [quoted text clipped - 18 lines] Brian -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum every nth cell in a row
thanks T.Valko
After looking at the link, realised, I gave you wrong info, my range was in a one row, different columns changed "row" to "column" and presto worked ! Thanks for your patients brian Thanks fo T. Valko wrote: entered with shift+ctl+ent Doesn't need to be array entered but that won't cause an error. Just ENTER will work. Keep getting #Value! error Are there any #VALUE! errors already in the range? Here's a small sample file that demonstrates this. xSumNth.xls 14kb http://cjoint.com/?dvxr6QVxsY Keep getting #Value! error [quoted text clipped - 30 lines] Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200803/1 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum every nth cell in a row
Glad you got it straightened out. Thanks for the feedback!
-- Biff Microsoft Excel MVP "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:818ab09a2bc1f@uwe... thanks T.Valko After looking at the link, realised, I gave you wrong info, my range was in a one row, different columns changed "row" to "column" and presto worked ! Thanks for your patients brian Thanks fo T. Valko wrote: entered with shift+ctl+ent Doesn't need to be array entered but that won't cause an error. Just ENTER will work. Keep getting #Value! error Are there any #VALUE! errors already in the range? Here's a small sample file that demonstrates this. xSumNth.xls 14kb http://cjoint.com/?dvxr6QVxsY Keep getting #Value! error [quoted text clipped - 30 lines] Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200803/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
cell data not validated if navigating cell to cell with mouse | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |