Microsoft Office Forums 5000 rows, want to select and perform addition on ever 48 rows

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-09-2019, 10:09 PM
Rexmaverick Rexmaverick is offline 5000 rows, want to select and perform addition on ever 48 rows Windows 10 5000 rows, want to select and perform addition on ever 48 rows Office 2016
Novice
5000 rows, want to select and perform addition on ever 48 rows
 
Join Date: Jun 2019
Posts: 3
Rexmaverick is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 06-09-2019, 10:47 PM
ArviLaanemets ArviLaanemets is offline 5000 rows, want to select and perform addition on ever 48 rows Windows 8 5000 rows, want to select and perform addition on ever 48 rows Office 2016
Expert
 
Join Date: May 2017
Posts: 448
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 06-09-2019, 11:03 PM
Rexmaverick Rexmaverick is offline 5000 rows, want to select and perform addition on ever 48 rows Windows 10 5000 rows, want to select and perform addition on ever 48 rows Office 2016
Novice
5000 rows, want to select and perform addition on ever 48 rows
 
Join Date: Jun 2019
Posts: 3
Rexmaverick is on a distinguished road
Default

Hi yes same number of rows, it is a simple formula, I am trying to avoid copy and pasting 100 times.
Reply With Quote
  #4  
Old 06-09-2019, 11:07 PM
Rexmaverick Rexmaverick is offline 5000 rows, want to select and perform addition on ever 48 rows Windows 10 5000 rows, want to select and perform addition on ever 48 rows Office 2016
Novice
5000 rows, want to select and perform addition on ever 48 rows
 
Join Date: Jun 2019
Posts: 3
Rexmaverick is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 06-10-2019, 01:19 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline 5000 rows, want to select and perform addition on ever 48 rows Windows 7 64bit 5000 rows, want to select and perform addition on ever 48 rows Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,383
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #6  
Old 06-10-2019, 06:29 AM
p45cal p45cal is offline 5000 rows, want to select and perform addition on ever 48 rows Windows 10 5000 rows, want to select and perform addition on ever 48 rows Office 2016
Expert
 
Join Date: Apr 2014
Posts: 257
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


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
5000 rows, want to select and perform addition on ever 48 rows Select rows w/cells containing given character strings, then sum cell in two other col. LarryHills Excel 1 02-17-2017 03:55 PM
5000 rows, want to select and perform addition on ever 48 rows Delete All empty Rows - Print - Undo all Rows deleted Bathroth Word VBA 1 10-01-2014 01:40 PM
5000 rows, want to select and perform addition on ever 48 rows Grouping table rows to prevent individual rows from breaking across pages dennist77 Word 1 10-29-2013 11:39 PM


All times are GMT -7. The time now is 10:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft