Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of every nth cell in a column
I am trying to figure out the formula for adding the nth cell in a given
column. I am using the formula below to add every 4th cell but it is just one that I found on line. Can someone explain to me the components of this formula so I can adapt it? =SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of every nth cell in a column
That is an array formula so it must be committed with Shift + Ctrl + <Enter.
here is how it works... Moving down through cells C2:C213 it looks at each 2 things. The value of the cell and the row that cell is on. If the row that cell is on is +1 is evenly divisible by 1 then it multiplies the value of that cell by 1. If it is not evenly divisible it multiplies the value by zero. So Cell C3 is on row 3. 3 + 1 is evenly divisible by 1 so it multiples the value of the cell by 1. C7 is 7+1 which is divisible... In the end it just adds the total. Anything on a row not evenly divisible was multiplied by 0. Anything multipled by 1 is itself. Row returns the row of the cell. Mod returns the remainder of the division. -- HTH... Jim Thomlinson "SnackFoodGirl" wrote: I am trying to figure out the formula for adding the nth cell in a given column. I am using the formula below to add every 4th cell but it is just one that I found on line. Can someone explain to me the components of this formula so I can adapt it? =SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of every nth cell in a column
Let me try explaining this
The ROW function returns the row number of a single cell reference ROW(reference) The MOD function returns the remainder after number is divided by divisor MOD(number, divisor) Nested the ROW function in the MOD function which is to supply the number argument, divided by 2 which is to sum every second cell will produced the result of 0 For example, to sum every second cell in the range A1:A20, the formula could be this =SUM(IF(MOD(ROW($A$1:$A$20),2=0,$A$1:$A$20,0)) Take note that this is an array formula which you must press Ctrl+Shift+Enter at the same time to produce the desire result. Excel will add the curly brackets to the formula A better option is to use the DSUM function -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "SnackFoodGirl" wrote: I am trying to figure out the formula for adding the nth cell in a given column. I am using the formula below to add every 4th cell but it is just one that I found on line. Can someone explain to me the components of this formula so I can adapt it? =SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of every nth cell in a column
=SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0))
Not sure why they have that +1 in there. As written that formula would sum cells starting at C3 so why are they referencing the range starting ar C2? What cells do you want to sum? C2, C6, C10, C14, C18, etc.? -- Biff Microsoft Excel MVP "SnackFoodGirl" wrote in message ... I am trying to figure out the formula for adding the nth cell in a given column. I am using the formula below to add every 4th cell but it is just one that I found on line. Can someone explain to me the components of this formula so I can adapt it? =SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique matches | Excel Worksheet Functions | |||
Sum Cell Values of one column based on Another Cell Value in a different column | Excel Worksheet Functions | |||
Need the formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
Need Formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
Format cell in column B based on value in the next cell (column c) | Excel Discussion (Misc queries) |