Dragging a formula

[Sasikiran]

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

 

[Jarek Kujawa]

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..."

 

[Mike H]

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

 

[Jarek Kujawa]

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

 

[Mike H]

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

 

[Sasikiran]

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]

Simply change the last 4 to a 5

=SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,5))

Mike

 

[Mike H]

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

 

[Jarek Kujawa]

;-)))

Simply change the last 4 to a 5

=SUM(OFFSET(B$14,(ROW(1:1)-1)*48,,5))

Mike








- Poka¿ cytowany tekst -

 

[Sasikiran]

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]

thks
could you pls click YES if any of the posts were helpful?
;-)