Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I add down a list a specified number of cells?
I have a huge number of lists that I am calculating. I have a vertical list
of numbers each vertical cell represents a week. I am trying to add up a varing number of week forward to madel stock controls. Currently I am adding up each individual cell for the number of week that i need (ie 4 weeks is =sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to write a formula that adds up a specified number of cells forward. i.e add ten cells down the list and return total? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I add down a list a specified number of cells?
Imagine you put the number of weeks you want to add in cell F1 (eg
10). Then you could use this formula: =SUM(INDIRECT("A2:A"&F1+1) Hope this helps. Pete On Nov 30, 12:43 pm, porbeagle wrote: I have a huge number of lists that I am calculating. I have a vertical list of numbers each vertical cell represents a week. I am trying to add up a varing number of week forward to madel stock controls. Currently I am adding up each individual cell for the number of week that i need (ie 4 weeks is =sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to write a formula that adds up a specified number of cells forward. i.e add ten cells down the list and return total? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I add down a list a specified number of cells?
Try something like this:
B1: (number of cells to add.....eg 10) A1: =SUM(A2:INDEX(A:A,B1)) In that example, the formula in A1 sums cells A2:A10 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "porbeagle" wrote in message ... I have a huge number of lists that I am calculating. I have a vertical list of numbers each vertical cell represents a week. I am trying to add up a varing number of week forward to madel stock controls. Currently I am adding up each individual cell for the number of week that i need (ie 4 weeks is =sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to write a formula that adds up a specified number of cells forward. i.e add ten cells down the list and return total? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I add down a list a specified number of cells?
=SUM(OFFSET(A2,0,0,4,1))
will give you the 4 week total in your example below. If you were to put this formula in (say) B2 and then copy it to B3, it would give you a four week total starting from A3. Of course you could put a cell address in instead of the number 4, for example =SUM(OFFSET(A2,0,0,G17,1)) Then it would total however many weeks were entered into G17. Or you could put a formula in there, and so on... "porbeagle" wrote in message ... I have a huge number of lists that I am calculating. I have a vertical list of numbers each vertical cell represents a week. I am trying to add up a varing number of week forward to madel stock controls. Currently I am adding up each individual cell for the number of week that i need (ie 4 weeks is =sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to write a formula that adds up a specified number of cells forward. i.e add ten cells down the list and return total? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I add down a list a specified number of cells?
Clarification:
The value in cell B1 actually indicates the Row Number to add through. To use it to indicate how many cells to add use this formula: A1: =SUM(A2:INDEX(A:A,ROW(A2)+B1-1)) -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... Try something like this: B1: (number of cells to add.....eg 10) A1: =SUM(A2:INDEX(A:A,B1)) In that example, the formula in A1 sums cells A2:A10 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "porbeagle" wrote in message ... I have a huge number of lists that I am calculating. I have a vertical list of numbers each vertical cell represents a week. I am trying to add up a varing number of week forward to madel stock controls. Currently I am adding up each individual cell for the number of week that i need (ie 4 weeks is =sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to write a formula that adds up a specified number of cells forward. i.e add ten cells down the list and return total? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I add down a list a specified number of cells?
Sorry, missed a bracket off:
=SUM(INDIRECT("A2:A"&F1+1)) Pete On Nov 30, 1:06 pm, Pete_UK wrote: Imagine you put the number of weeks you want to add in cell F1 (eg 10). Then you could use this formula: =SUM(INDIRECT("A2:A"&F1+1) Hope this helps. Pete On Nov 30, 12:43 pm, porbeagle wrote: I have a huge number of lists that I am calculating. I have a vertical list of numbers each vertical cell represents a week. I am trying to add up a varing number of week forward to madel stock controls. Currently I am adding up each individual cell for the number of week that i need (ie 4 weeks is =sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to write a formula that adds up a specified number of cells forward. i.e add ten cells down the list and return total?- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I add down a list a specified number of cells?
Thanks!!!!
Both these formula's work (although I am yet to work out how). Although due to my column text headers I have added +4 to the end of each formula (where you had +1 Pete and after your B1 Pete) to start the count from a few rows down. Thanks guys you have turned a whole afternoon's work into a few hours, at the means alot on a Friday!!! "Ron Coderre" wrote: Try something like this: B1: (number of cells to add.....eg 10) A1: =SUM(A2:INDEX(A:A,B1)) In that example, the formula in A1 sums cells A2:A10 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "porbeagle" wrote in message ... I have a huge number of lists that I am calculating. I have a vertical list of numbers each vertical cell represents a week. I am trying to add up a varing number of week forward to madel stock controls. Currently I am adding up each individual cell for the number of week that i need (ie 4 weeks is =sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to write a formula that adds up a specified number of cells forward. i.e add ten cells down the list and return total? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I add down a list a specified number of cells?
You're welcome!
Thanks for letting us know that we helped! *********** Regards, Ron XL2003, WinXP "porbeagle" wrote: Thanks!!!! Both these formula's work (although I am yet to work out how). Although due to my column text headers I have added +4 to the end of each formula (where you had +1 Pete and after your B1 Pete) to start the count from a few rows down. Thanks guys you have turned a whole afternoon's work into a few hours, at the means alot on a Friday!!! "Ron Coderre" wrote: Try something like this: B1: (number of cells to add.....eg 10) A1: =SUM(A2:INDEX(A:A,B1)) In that example, the formula in A1 sums cells A2:A10 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "porbeagle" wrote in message ... I have a huge number of lists that I am calculating. I have a vertical list of numbers each vertical cell represents a week. I am trying to add up a varing number of week forward to madel stock controls. Currently I am adding up each individual cell for the number of week that i need (ie 4 weeks is =sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to write a formula that adds up a specified number of cells forward. i.e add ten cells down the list and return total? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I add down a list a specified number of cells?
That's good to hear, and thanks for the feedback. In my formula, if F1
contained 10, then what it is doing is: SUM(INDIRECT("A2:A"&F1+1)), which becomes: =SUM(INDIRECT("A2:A"&11)), or: =SUM(INDIRECT("A2:A11")), or: =SUM(A2:A11) i.e. the reference to the last cell in the range is created by adding 1 onto the number in F1. This is fine if your numbers start in A2, but if they started in A5, say, and you wanted 10 of them, then you would want the range to be A5:A14, in which case you would have to add 4 onto F1 to get the last cell. Hope this explains a bit further. Pete On Nov 30, 1:40 pm, porbeagle wrote: Thanks!!!! Both these formula's work (although I am yet to work out how). Although due to my column text headers I have added +4 to the end of each formula (where you had +1 Pete and after your B1 Pete) to start the count from a few rows down. Thanks guys you have turned a whole afternoon's work into a few hours, at the means alot on a Friday!!! "Ron Coderre" wrote: Try something like this: B1: (number of cells to add.....eg 10) A1: =SUM(A2:INDEX(A:A,B1)) In that example, the formula in A1 sums cells A2:A10 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "porbeagle" wrote in message ... I have a huge number of lists that I am calculating. I have a vertical list of numbers each vertical cell represents a week. I am trying to add up a varing number of week forward to madel stock controls. Currently I am adding up each individual cell for the number of week that i need (ie 4 weeks is =sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to write a formula that adds up a specified number of cells forward. i.e add ten cells down the list and return total?- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I add down a list a specified number of cells?
Other contributors have given a variety of answers to your question, but
would you like to enlighten us by telling us what value you think the SUM() function adds to your formula? What does =sum(a2+a3+a4+a5) do for you that =(a2+a3+a4+a5) doesn't? You may wish to look up the SUM() function in Excel help. -- David Biddulph "porbeagle" wrote in message ... I have a huge number of lists that I am calculating. I have a vertical list of numbers each vertical cell represents a week. I am trying to add up a varing number of week forward to madel stock controls. Currently I am adding up each individual cell for the number of week that i need (ie 4 weeks is =sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to write a formula that adds up a specified number of cells forward. i.e add ten cells down the list and return total? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to know the number of times "fam" appears in a list of cells | Excel Discussion (Misc queries) | |||
How do I number the cells when I am making a list? | Excel Worksheet Functions | |||
How do i set up a list that sorts as Number/letter/number in orde | Excel Discussion (Misc queries) | |||
change info in other cells when i change a number in a drop list? | Excel Discussion (Misc queries) | |||
How do I count the number of cells in a list, e.g. H6:H12, J4, J7: | Excel Worksheet Functions |