| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding every sixth value
I have to add every sixth value in a column summed over 52 value. Is there
an equation to do this so I don't have to input each cell. Example Column 1 2 3 4 5 1 2 3 4 5 So the equation I need is Column (1+1), Column (2+2), so on |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding every sixth value
If you went from Column A to Column IV, *only* the "first column series"
would contain 52 values. All the rest would have 51. That being the case, this formula will total column A to IV on Row10, starting in A: =SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10)) Starting in B: =SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10)) Starting in C: =SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10)) Starting in D: =SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10)) Starting in E: =SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "KLock" wrote in message ... I have to add every sixth value in a column summed over 52 value. Is there an equation to do this so I don't have to input each cell. Example Column 1 2 3 4 5 1 2 3 4 5 So the equation I need is Column (1+1), Column (2+2), so on |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding every sixth value
Ok that probably will work the only problems are
I need to start with column B, since column A is the header, and the equation I am inputting is at the end of the row, so I don't want to add these cells. "RagDyeR" wrote: If you went from Column A to Column IV, *only* the "first column series" would contain 52 values. All the rest would have 51. That being the case, this formula will total column A to IV on Row10, starting in A: =SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10)) Starting in B: =SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10)) Starting in C: =SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10)) Starting in D: =SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10)) Starting in E: =SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "KLock" wrote in message ... I have to add every sixth value in a column summed over 52 value. Is there an equation to do this so I don't have to input each cell. Example Column 1 2 3 4 5 1 2 3 4 5 So the equation I need is Column (1+1), Column (2+2), so on |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding every sixth value
Since Column A has the headers and I inputting the equation in IH9
I changed the equation to =SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9)) but the calculations are wrong is there anything else I need to change for this adjustment or is this just the wrong equation for this situation "KLock" wrote: Ok that probably will work the only problems are I need to start with column B, since column A is the header, and the equation I am inputting is at the end of the row, so I don't want to add these cells. "RagDyeR" wrote: If you went from Column A to Column IV, *only* the "first column series" would contain 52 values. All the rest would have 51. That being the case, this formula will total column A to IV on Row10, starting in A: =SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10)) Starting in B: =SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10)) Starting in C: =SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10)) Starting in D: =SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10)) Starting in E: =SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "KLock" wrote in message ... I have to add every sixth value in a column summed over 52 value. Is there an equation to do this so I don't have to input each cell. Example Column 1 2 3 4 5 1 2 3 4 5 So the equation I need is Column (1+1), Column (2+2), so on |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding every sixth value
Starting in B:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+3,5)=0)*(B9:IG9)) Starting in C: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+2,5)=0)*(B9:IG9)) Starting in D: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+1,5)=0)*(B9:IG9)) Starting in E: =SUMPRODUCT((MOD(COLUMN(B9:IG9),5)=0)*(B9:IG9)) Starting in F: =SUMPRODUCT((MOD(COLUMN(B9:IG9)-1,5)=0)*(B9:IG9)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "KLock" wrote in message ... Since Column A has the headers and I inputting the equation in IH9 I changed the equation to =SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9)) but the calculations are wrong is there anything else I need to change for this adjustment or is this just the wrong equation for this situation "KLock" wrote: Ok that probably will work the only problems are I need to start with column B, since column A is the header, and the equation I am inputting is at the end of the row, so I don't want to add these cells. "RagDyeR" wrote: If you went from Column A to Column IV, *only* the "first column series" would contain 52 values. All the rest would have 51. That being the case, this formula will total column A to IV on Row10, starting in A: =SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10)) Starting in B: =SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10)) Starting in C: =SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10)) Starting in D: =SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10)) Starting in E: =SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "KLock" wrote in message ... I have to add every sixth value in a column summed over 52 value. Is there an equation to do this so I don't have to input each cell. Example Column 1 2 3 4 5 1 2 3 4 5 So the equation I need is Column (1+1), Column (2+2), so on |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding every sixth value
Ok that works thanks for the help
"RagDyer" wrote: Starting in B: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+3,5)=0)*(B9:IG9)) Starting in C: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+2,5)=0)*(B9:IG9)) Starting in D: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+1,5)=0)*(B9:IG9)) Starting in E: =SUMPRODUCT((MOD(COLUMN(B9:IG9),5)=0)*(B9:IG9)) Starting in F: =SUMPRODUCT((MOD(COLUMN(B9:IG9)-1,5)=0)*(B9:IG9)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "KLock" wrote in message ... Since Column A has the headers and I inputting the equation in IH9 I changed the equation to =SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9)) but the calculations are wrong is there anything else I need to change for this adjustment or is this just the wrong equation for this situation "KLock" wrote: Ok that probably will work the only problems are I need to start with column B, since column A is the header, and the equation I am inputting is at the end of the row, so I don't want to add these cells. "RagDyeR" wrote: If you went from Column A to Column IV, *only* the "first column series" would contain 52 values. All the rest would have 51. That being the case, this formula will total column A to IV on Row10, starting in A: =SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10)) Starting in B: =SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10)) Starting in C: =SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10)) Starting in D: =SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10)) Starting in E: =SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "KLock" wrote in message ... I have to add every sixth value in a column summed over 52 value. Is there an equation to do this so I don't have to input each cell. Example Column 1 2 3 4 5 1 2 3 4 5 So the equation I need is Column (1+1), Column (2+2), so on |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding every sixth value
If you want every 6th value, I would have thought that you'd need MOD(...,6)
and not MOD(...,5), but the OP's example looked as if he might want every 5th though he said every 6th? -- David Biddulph "RagDyer" wrote in message ... Starting in B: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+3,5)=0)*(B9:IG9)) Starting in C: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+2,5)=0)*(B9:IG9)) Starting in D: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+1,5)=0)*(B9:IG9)) Starting in E: =SUMPRODUCT((MOD(COLUMN(B9:IG9),5)=0)*(B9:IG9)) Starting in F: =SUMPRODUCT((MOD(COLUMN(B9:IG9)-1,5)=0)*(B9:IG9)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "KLock" wrote in message ... Since Column A has the headers and I inputting the equation in IH9 I changed the equation to =SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9)) but the calculations are wrong is there anything else I need to change for this adjustment or is this just the wrong equation for this situation "KLock" wrote: Ok that probably will work the only problems are I need to start with column B, since column A is the header, and the equation I am inputting is at the end of the row, so I don't want to add these cells. "RagDyeR" wrote: If you went from Column A to Column IV, *only* the "first column series" would contain 52 values. All the rest would have 51. That being the case, this formula will total column A to IV on Row10, starting in A: =SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10)) Starting in B: =SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10)) Starting in C: =SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10)) Starting in D: =SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10)) Starting in E: =SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "KLock" wrote in message ... I have to add every sixth value in a column summed over 52 value. Is there an equation to do this so I don't have to input each cell. Example Column 1 2 3 4 5 1 2 3 4 5 So the equation I need is Column (1+1), Column (2+2), so on |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding every sixth value
You're welcome, and thank you for the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "KLock" wrote in message ... Ok that works thanks for the help "RagDyer" wrote: Starting in B: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+3,5)=0)*(B9:IG9)) Starting in C: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+2,5)=0)*(B9:IG9)) Starting in D: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+1,5)=0)*(B9:IG9)) Starting in E: =SUMPRODUCT((MOD(COLUMN(B9:IG9),5)=0)*(B9:IG9)) Starting in F: =SUMPRODUCT((MOD(COLUMN(B9:IG9)-1,5)=0)*(B9:IG9)) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "KLock" wrote in message ... Since Column A has the headers and I inputting the equation in IH9 I changed the equation to =SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9)) but the calculations are wrong is there anything else I need to change for this adjustment or is this just the wrong equation for this situation "KLock" wrote: Ok that probably will work the only problems are I need to start with column B, since column A is the header, and the equation I am inputting is at the end of the row, so I don't want to add these cells. "RagDyeR" wrote: If you went from Column A to Column IV, *only* the "first column series" would contain 52 values. All the rest would have 51. That being the case, this formula will total column A to IV on Row10, starting in A: =SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10)) Starting in B: =SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10)) Starting in C: =SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10)) Starting in D: =SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10)) Starting in E: =SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "KLock" wrote in message ... I have to add every sixth value in a column summed over 52 value. Is there an equation to do this so I don't have to input each cell. Example Column 1 2 3 4 5 1 2 3 4 5 So the equation I need is Column (1+1), Column (2+2), so on |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding every sixth value
It's usually a good idea to give examples preference over text.<g
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... If you want every 6th value, I would have thought that you'd need MOD(...,6) and not MOD(...,5), but the OP's example looked as if he might want every 5th though he said every 6th? -- David Biddulph "RagDyer" wrote in message ... Starting in B: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+3,5)=0)*(B9:IG9)) Starting in C: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+2,5)=0)*(B9:IG9)) Starting in D: =SUMPRODUCT((MOD(COLUMN(B9:IG9)+1,5)=0)*(B9:IG9)) Starting in E: =SUMPRODUCT((MOD(COLUMN(B9:IG9),5)=0)*(B9:IG9)) Starting in F: =SUMPRODUCT((MOD(COLUMN(B9:IG9)-1,5)=0)*(B9:IG9)) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "KLock" wrote in message ... Since Column A has the headers and I inputting the equation in IH9 I changed the equation to =SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9)) but the calculations are wrong is there anything else I need to change for this adjustment or is this just the wrong equation for this situation "KLock" wrote: Ok that probably will work the only problems are I need to start with column B, since column A is the header, and the equation I am inputting is at the end of the row, so I don't want to add these cells. "RagDyeR" wrote: If you went from Column A to Column IV, *only* the "first column series" would contain 52 values. All the rest would have 51. That being the case, this formula will total column A to IV on Row10, starting in A: =SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10)) Starting in B: =SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10)) Starting in C: =SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10)) Starting in D: =SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10)) Starting in E: =SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "KLock" wrote in message ... I have to add every sixth value in a column summed over 52 value. Is there an equation to do this so I don't have to input each cell. Example Column 1 2 3 4 5 1 2 3 4 5 So the equation I need is Column (1+1), Column (2+2), so on |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In addicting across columns, every sixth column, I loose .01 | New Users to Excel | |||
| Adding Up! | Excel Discussion (Misc queries) | |||
| Adding Up! | Excel Discussion (Misc queries) | |||
| Adding an OR to SUM(IF.... | Excel Worksheet Functions | |||
| Adding | Excel Discussion (Misc queries) | |||
Linear Mode
