| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dragging a formula
Dear,
I need to drag the formula in a column in such a way that the formula should proceed in this manner. SUM(B14:B18) SUM(B62:B68) SUM(B110:B114) it should calculate the sum range of 4 continuos cells after every 48th cell... Hope this is clear Please suggest |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dragging a formula
in C1:
=SUM(INDIRECT("B"&(14+48*(ROW()-1))&":B""&(18+48*(ROW()-1))) however pls pay attention to the fact that B62:B68 does not follow your desired pattern: "it should calculate the sum range of 4 continuos cells after every 48th cell..." On 14 Mar, 09:34, Sasikiran wrote: Dear, I need to drag the formula in a column in such a way that the formula should proceed in this manner. SUM(B14:B18) SUM(B62:B68) * * * * SUM(B110:B114) it should calculate the sum range of 4 continuos cells after every 48th cell... Hope this is clear Please suggest |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dragging a formula
may be this is what yo're looking for:
in C1 =SUM(INDIRECT("B"&(14+48*(ROW()-1))&":B""&(18+48*(ROW()-1))) in C2 =SUM(INDIRECT("B"&(14+48*(ROW()-1))&":B""&(20+48*(ROW()-1))) select C1:C2 then drag down On 14 Mar, 09:58, Jarek Kujawa wrote: in C1: =SUM(INDIRECT("B"&(14+48*(ROW()-1))&":B""&(18+48*(ROW()-1))) however pls pay attention to the fact that B62:B68 does not follow your desired pattern: "it should calculate the sum range of 4 continuos cells after every 48th cell..." On 14 Mar, 09:34, Sasikiran wrote: Dear, I need to drag the formula in a column in such a way that the formula should proceed in this manner. SUM(B14:B18) SUM(B62:B68) * * * * SUM(B110:B114) it should calculate the sum range of 4 continuos cells after every 48th cell... Hope this is clear Please suggest- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dragging a formula
Hi,
To sum 4 rows every 48 i'm sure you meant this =SUM(INDIRECT("B"&(14+48*(ROW()-1))&":B"&(17+48*(ROW()-1)))) Mike "Jarek Kujawa" wrote: may be this is what yo're looking for: in C1 =SUM(INDIRECT("B"&(14+48*(ROW()-1))&":B""&(18+48*(ROW()-1))) in C2 =SUM(INDIRECT("B"&(14+48*(ROW()-1))&":B""&(20+48*(ROW()-1))) select C1:C2 then drag down On 14 Mar, 09:58, Jarek Kujawa wrote: in C1: =SUM(INDIRECT("B"&(14+48*(ROW()-1))&":B""&(18+48*(ROW()-1))) however pls pay attention to the fact that B62:B68 does not follow your desired pattern: "it should calculate the sum range of 4 continuos cells after every 48th cell..." On 14 Mar, 09:34, Sasikiran wrote: Dear, I need to drag the formula in a column in such a way that the formula should proceed in this manner. SUM(B14:B18) SUM(B62:B68) SUM(B110:B114) it should calculate the sum range of 4 continuos cells after every 48th cell... Hope this is clear Please suggest- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dragging a formula
Hi,
The example you gives is confusing SUM(B14:B18) = 5 Cells SUM(B62:B68) = 7 cells SUM(B110:B114) = 5 Cells Compared to what you ask for it should calculate the sum range of 4 continuos cells after every 48th cell... So I'll guess and sum 4 cells every 48 cells, try this and drag down =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Mike "Sasikiran" wrote: Dear, I need to drag the formula in a column in such a way that the formula should proceed in this manner. SUM(B14:B18) SUM(B62:B68) SUM(B110:B114) it should calculate the sum range of 4 continuos cells after every 48th cell... Hope this is clear Please suggest |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dragging a formula
Dear Mike,
Sorry for the typo error... You got that right thing what I'm trying to explain... but the below formula is not working :( =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Everytime I drag the formula down, it should refer to the 48th cell (B14+48 -- B62) and should calculate range of 5 continuos cells. SUM(B14:B18) = 5 Cells +48 +48 SUM(B62:B66) = 5 cells +48 +48 SUM(B110:B114) = 5 Cells +48 +48 And so on.... "Mike H" wrote: Hi, The example you gives is confusing SUM(B14:B18) = 5 Cells SUM(B62:B68) = 7 cells SUM(B110:B114) = 5 Cells Compared to what you ask for it should calculate the sum range of 4 continuos cells after every 48th cell... So I'll guess and sum 4 cells every 48 cells, try this and drag down =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Mike "Sasikiran" wrote: Dear, I need to drag the formula in a column in such a way that the formula should proceed in this manner. SUM(B14:B18) SUM(B62:B68) SUM(B110:B114) it should calculate the sum range of 4 continuos cells after every 48th cell... Hope this is clear Please suggest |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dragging a formula
Simply change the last 4 to a 5
=SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,5)) Mike "Sasikiran" wrote: Dear Mike, Sorry for the typo error... You got that right thing what I'm trying to explain... but the below formula is not working :( =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Everytime I drag the formula down, it should refer to the 48th cell (B14+48 -- B62) and should calculate range of 5 continuos cells. SUM(B14:B18) = 5 Cells +48 +48 SUM(B62:B66) = 5 cells +48 +48 SUM(B110:B114) = 5 Cells +48 +48 And so on.... "Mike H" wrote: Hi, The example you gives is confusing SUM(B14:B18) = 5 Cells SUM(B62:B68) = 7 cells SUM(B110:B114) = 5 Cells Compared to what you ask for it should calculate the sum range of 4 continuos cells after every 48th cell... So I'll guess and sum 4 cells every 48 cells, try this and drag down =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Mike "Sasikiran" wrote: Dear, I need to drag the formula in a column in such a way that the formula should proceed in this manner. SUM(B14:B18) SUM(B62:B68) SUM(B110:B114) it should calculate the sum range of 4 continuos cells after every 48th cell... Hope this is clear Please suggest |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dragging a formula
Hi,
I should explain the formula B$14 is where we start 48 is the step 5 is the number of rows to sum =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,5)) Mike "Mike H" wrote: Simply change the last 4 to a 5 =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,5)) Mike "Sasikiran" wrote: Dear Mike, Sorry for the typo error... You got that right thing what I'm trying to explain... but the below formula is not working :( =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Everytime I drag the formula down, it should refer to the 48th cell (B14+48 -- B62) and should calculate range of 5 continuos cells. SUM(B14:B18) = 5 Cells +48 +48 SUM(B62:B66) = 5 cells +48 +48 SUM(B110:B114) = 5 Cells +48 +48 And so on.... "Mike H" wrote: Hi, The example you gives is confusing SUM(B14:B18) = 5 Cells SUM(B62:B68) = 7 cells SUM(B110:B114) = 5 Cells Compared to what you ask for it should calculate the sum range of 4 continuos cells after every 48th cell... So I'll guess and sum 4 cells every 48 cells, try this and drag down =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Mike "Sasikiran" wrote: Dear, I need to drag the formula in a column in such a way that the formula should proceed in this manner. SUM(B14:B18) SUM(B62:B68) SUM(B110:B114) it should calculate the sum range of 4 continuos cells after every 48th cell... Hope this is clear Please suggest |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dragging a formula
;-)))
On 14 Mar, 10:37, Mike H wrote: Simply change the last 4 to a 5 =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,5)) Mike "Sasikiran" wrote: Dear Mike, Sorry for the typo error... You got that right thing what I'm trying to explain... but the below formula is not working :( =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Everytime I drag the formula down, it should refer to the 48th cell (B14+48 -- B62) and should calculate range of 5 continuos cells. SUM(B14:B18) = 5 Cells * * * * +48 +48 SUM(B62:B66) *= 5 cells * * * * +48 +48 SUM(B110:B114) = 5 Cells * * * * +48 +48 And so on.... "Mike H" wrote: Hi, The example you gives is confusing SUM(B14:B18) = 5 Cells SUM(B62:B68) *= 7 cells SUM(B110:B114) = 5 Cells Compared to what you ask for it should calculate the sum range of 4 continuos cells after every 48th cell... So I'll guess and sum 4 cells every 48 cells, try this and drag down =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Mike "Sasikiran" wrote: Dear, I need to drag the formula in a column in such a way that the formula should proceed in this manner. SUM(B14:B18) SUM(B62:B68) * * * * SUM(B110:B114) it should calculate the sum range of 4 continuos cells after every 48th cell... Hope this is clear Please suggest- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dragging a formula
Thanks a ton Mike and Jarek...
Really appreciate your suggestions to make my job easier... :) Continue to thrill all the users in the same way. Thanks once again... "Jarek Kujawa" wrote: ;-))) On 14 Mar, 10:37, Mike H wrote: Simply change the last 4 to a 5 =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,5)) Mike "Sasikiran" wrote: Dear Mike, Sorry for the typo error... You got that right thing what I'm trying to explain... but the below formula is not working :( =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Everytime I drag the formula down, it should refer to the 48th cell (B14+48 -- B62) and should calculate range of 5 continuos cells. SUM(B14:B18) = 5 Cells +48 +48 SUM(B62:B66) = 5 cells +48 +48 SUM(B110:B114) = 5 Cells +48 +48 And so on.... "Mike H" wrote: Hi, The example you gives is confusing SUM(B14:B18) = 5 Cells SUM(B62:B68) = 7 cells SUM(B110:B114) = 5 Cells Compared to what you ask for it should calculate the sum range of 4 continuos cells after every 48th cell... So I'll guess and sum 4 cells every 48 cells, try this and drag down =SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,4)) Mike "Sasikiran" wrote: Dear, I need to drag the formula in a column in such a way that the formula should proceed in this manner. SUM(B14:B18) SUM(B62:B68) SUM(B110:B114) it should calculate the sum range of 4 continuos cells after every 48th cell... Hope this is clear Please suggest- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dragging a formula | Excel Discussion (Misc queries) | |||
| dragging my formula | Excel Discussion (Misc queries) | |||
| Dragging Down a Formula | Excel Worksheet Functions | |||
| Dragging formula | Excel Discussion (Misc queries) | |||
| dragging a formula | Excel Discussion (Misc queries) | |||
Hybrid Mode
