If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Summing a variable range
I need to create a formula for summing cells say B2 through J2 with results
in A2 so I use the formula =sum(B2:J2) As you see I am summing 9 cells. What I want to do is able to type a number in Cell A1 and sum the B2 to A1 number of cells to the right of B2. Any help will be appreciated. |
#2
|
|||
|
|||
Summing a variable range
=SUM(OFFSET(B2,0,0,1,A1))
Regards, Stefi „Ram B” ezt *rta: I need to create a formula for summing cells say B2 through J2 with results in A2 so I use the formula =sum(B2:J2) As you see I am summing 9 cells. What I want to do is able to type a number in Cell A1 and sum the B2 to A1 number of cells to the right of B2. Any help will be appreciated. |
#3
|
|||
|
|||
Summing a variable range
=SUM(INDIRECT("B2:"&LEFT(ADDRESS(1,A1+1,2),FIND("$ ",ADDRESS(1,A1+1,2))-1)&"2"))
-- John C "Ram B" wrote: I need to create a formula for summing cells say B2 through J2 with results in A2 so I use the formula =sum(B2:J2) As you see I am summing 9 cells. What I want to do is able to type a number in Cell A1 and sum the B2 to A1 number of cells to the right of B2. Any help will be appreciated. |
#4
|
|||
|
|||
Summing a variable range
Thanks it works
"Stefi" wrote: =SUM(OFFSET(B2,0,0,1,A1)) Regards, Stefi „Ram B” ezt *rta: I need to create a formula for summing cells say B2 through J2 with results in A2 so I use the formula =sum(B2:J2) As you see I am summing 9 cells. What I want to do is able to type a number in Cell A1 and sum the B2 to A1 number of cells to the right of B2. Any help will be appreciated. |
#5
|
|||
|
|||
Summing a variable range
Here's a non volatile solution
=SUM(B2:INDEX(B2:IV2,A1)) -- Regards, Peo Sjoblom "Ram B" wrote in message ... Thanks it works "Stefi" wrote: =SUM(OFFSET(B2,0,0,1,A1)) Regards, Stefi "Ram B" ezt rta: I need to create a formula for summing cells say B2 through J2 with results in A2 so I use the formula =sum(B2:J2) As you see I am summing 9 cells. What I want to do is able to type a number in Cell A1 and sum the B2 to A1 number of cells to the right of B2. Any help will be appreciated. |
#6
|
|||
|
|||
Summing a variable range
You are welcome! Thanks for the feedback!
Stefi „Ram B” ezt *rta: Thanks it works "Stefi" wrote: =SUM(OFFSET(B2,0,0,1,A1)) Regards, Stefi „Ram B” ezt *rta: I need to create a formula for summing cells say B2 through J2 with results in A2 so I use the formula =sum(B2:J2) As you see I am summing 9 cells. What I want to do is able to type a number in Cell A1 and sum the B2 to A1 number of cells to the right of B2. Any help will be appreciated. |
#7
|
|||
|
|||
Summing a variable range
Why do you call it a non volatile solution? As far as I know "volatile" means
that the expression is recalculated each time a Calculation occurs. It applies to your formula as well, doesn't it? Regards, Stefi „Peo Sjoblom” ezt *rta: Here's a non volatile solution =SUM(B2:INDEX(B2:IV2,A1)) -- Regards, Peo Sjoblom "Ram B" wrote in message ... Thanks it works "Stefi" wrote: =SUM(OFFSET(B2,0,0,1,A1)) Regards, Stefi "Ram B" ezt *rta: I need to create a formula for summing cells say B2 through J2 with results in A2 so I use the formula =sum(B2:J2) As you see I am summing 9 cells. What I want to do is able to type a number in Cell A1 and sum the B2 to A1 number of cells to the right of B2. Any help will be appreciated. |
Thread Tools | |
Display Modes | |
|
|