#1
|
|||
|
|||
5000 rows, want to select and perform addition on ever 48 rows
Hi,
Thank you for having a look, I have done this years ago and cannot remember how to achieve it again, As above I have approx 5000 rows, I wand to select O2:048 and put result in p48, then select O49:O97 and put result in P97, then O98:O145 and put result in P145. Appreciate any assistance Rex |
#2
|
|||
|
|||
Would it be the case that all ranges are same number of rows, a relatively simple formula would do. As from your example follows, the 1st range is 47 rows, the 2nd one 49 rows, the 3rd one 48 rows, ... . So unless you have some other criterion in some column of your table allowing to decide, to which range/group of data the row belongs, and which you forgot to include in your message, there is no way to use formulas to get wanted result.
Either you provide some additional info, or you write a procedure which calculates and inserts needed result, and call it through shortcut after you select a range. |
#3
|
|||
|
|||
Hi yes same number of rows, it is a simple formula, I am trying to avoid copy and pasting 100 times.
|
#4
|
|||
|
|||
Just found the old one, it is every 15 rows, =IF(MOD(C2,15)=0,E2,0) can drag this down and it does it.
Thanks anyway, always appreciated. |
#5
|
||||
|
||||
Your formula is based on MOD(C2,..
Except if C2 contains the number of rows starting at the beginning, it won't work Starting in O2, in P2 enter =IF(MOD(row(),49)=0,sum($O$2:$O2)-sum($P$1:$P1),"") and pull down
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
||||
|
||||
Put your formula: =SUM( O2:O48) into cell P48.
Select cells P2:P48 as a single block. Without de-selecting that range grab the drag-handle at the bottom left of cell P48 with the mouse and drag down as far as you need. You may have to adjust this a bit because your first sum sums 47 rows, your second one 49 rows and your third 48 rows. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need to apply Heading1 to select rows | bobk544 | Word VBA | 3 | 06-09-2018 05:24 AM |
How to find duplicate rows in a huge excel file containing 500000 rows | Stc25234 | Excel Programming | 2 | 06-23-2017 10:51 AM |
Select rows w/cells containing given character strings, then sum cell in two other col. | LarryHills | Excel | 1 | 02-17-2017 03:55 PM |
Delete All empty Rows - Print - Undo all Rows deleted | Bathroth | Word VBA | 1 | 10-01-2014 01:40 PM |
Grouping table rows to prevent individual rows from breaking across pages | dennist77 | Word | 1 | 10-29-2013 11:39 PM |