+ Reply to Thread
Results 1 to 12 of 12

How to stop sum(offset()) calculation at a particular column

  1. #1
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Question How to stop sum(offset()) calculation at a particular column

    Hello, I am using the following formula to work out the sales value for a customers first 12 months with us and then each consecutive 12 months after that. Each month is in the column header, and the customer reference is in column A. The data under each month is the total value of sales for that month. The formula finds the first non blank value for the customer and sums that cell plus the next 11 cells giving the first years totals
    Please Login or Register  to view this content.
    where A17 is the customer reference number and DU17 contains a formula to return the position in the row of the first non blank cell (my starting position).
    In the following column I have added the formula that follows to calculate the following 12 months for year 2
    Please Login or Register  to view this content.
    and the same for years 3,4, & 5 in the following 3 columns by increasing the '+12' to the appropriate number of months (24,36,48). This works fine however I have had to insert 25 blank columns between the end of the data and my formula columns in order to prevent circular references from occurring (to give enough room for all 5 years to be added to the latest start date. This means that I will have to keep inserting an empty column every time I add in the values for a new month. Does anyone know how to stop the sum at a certain column even if only part of the 12 cells have been summed. i.e. I don't want to sum any further than column BJ. Thanks.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: How to stop sum(offset()) calculation at a particular column

    Please post a sample file with example of results required.

  3. #3
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Question Re: How to stop sum(offset()) calculation at a particular column

    Example attached
    I need to be able to use the formulas in the end columns DT to DX inclusive but without all the blank columns after col BM. I need the formula to only go as far as col BM and then stop.
    thanks
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to stop sum(offset()) calculation at a particular column

    Change your array formula in EA5 to

    =MIN(MATCH(FALSE,ISBLANK(B5:BI5),0),48)

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to stop sum(offset()) calculation at a particular column

    I'm going to suggest that you ignore my previous suggestion, it seemed like a good idea at the time, now reality has kicked in it doesn't seem so good

    Try this one in DT5, then copy it right to DX5 and down to AX29. No need to edit for each column like you did with your old formula, this one fits all years.

    =IFERROR(SUM(OFFSET(INDEX($A5:$AX5,$EA5+(COLUMNS($DT5:DT5)-1)*12+1),0,0,1,12)),SUM($B5:$BI5)-SUM($DS5:DS5))

    The use of index within offset will force an error to be returned for any 'year' that effectively starts after column AX, i.e. the final, partial year, and any years that fall beyond the scope of available data (4th and 5th years on an account with only 2 1/2 years of data). This means that only whole years will be summed by offset, the final section, SUM()-SUM() then calculates the difference between the sales for the whole years and the total sales to return the figure for the final partial year, which also applies a zero sales result to the out of scope years.

    Deleting columns BM:DQ after entering the formula will automatically adjust the ranges used, with no circular references.
    Last edited by jason.b75; 01-29-2016 at 11:36 AM.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: How to stop sum(offset()) calculation at a particular column

    Try

    =SUM(OFFSET($A5,0,MATCH(1,INDEX(ISNUMBER($B5:$BL5)+0,0),0)+(COLUMNS($A:A)-1)*12,1,12))

    Copy across for your 5 years

    and then down

    Or Have I missed something (misunderstood problem) ?
    Last edited by JohnTopley; 01-29-2016 at 01:57 PM.

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to stop sum(offset()) calculation at a particular column

    I think that one of us has, John.

    I read the problem as needing to find a solution that returns the same results as the existing formula, without creating circular references if you remove the empty columns between the source data and the formula / results table.

    Looking as the way the data is analysed, the 5 year period runs from the date of the first record in the given row, pseudo formula =OFFSET(first sale,0,0,1,60), looking at row 10, where the first sale is in column BC, that makes the 60 column range BC10:DJ10, which extends well beyond the data range, and if you remove the empty columns and enter the results table into BM:BW, you can see that the range created by offset will overlap the results, causing circular references.

    My intention, by using INDEX to limit the OFFSET reference range, was to create a wall at the end of the data that OFFSET cannot pass through. INDEX($A5:$AX5 gives a limited range of 50 cells, if the row number passed to index exceeds 50 then an error is returned, preventing the formula from exceeding the specified range, going from the last cell you would get an offset formula of =SUM(OFFSET(AX5,0,0,0,12)) which would be equal to =SUM(AX5:BI5), eliminating the circular refs.

    If I have the requirement right, I can't help thinking that there is still a better way to do it.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: How to stop sum(offset()) calculation at a particular column

    @Jason,
    The penny has dropped! You have it right. The most practical alternative is to have the yearly totals on another sheet.

    Why make life complicated?!
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to stop sum(offset()) calculation at a particular column

    Quote Originally Posted by JohnTopley View Post
    Why make life complicated?!
    That appears to be my speciality, John

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: How to stop sum(offset()) calculation at a particular column

    @Jason,
    Thank you for the rep: much appreciated.

  11. #11
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: How to stop sum(offset()) calculation at a particular column

    Wonderful, Thank you so much John, so much simpler to put onto a second sheet

  12. #12
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: How to stop sum(offset()) calculation at a particular column

    Thank you so much for your help Jason.b75 - between you and John Topley you have solved my problem

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Code to stop vba from overwriting content of Offset cell
    By gwd in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-16-2014, 06:18 AM
  2. Replies: 0
    Last Post: 05-17-2014, 10:18 PM
  3. Stop all calculation
    By phil2006 in forum Excel General
    Replies: 2
    Last Post: 01-18-2014, 04:57 AM
  4. No/stop calculation when doing Text To Column function
    By morayman in forum Excel General
    Replies: 2
    Last Post: 12-02-2013, 02:43 AM
  5. [SOLVED] How to stop .End(xlip).Offset(0, 0) at a certain point?
    By tnuis in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-04-2013, 02:44 PM
  6. [SOLVED] How to stop OFFSET including one blank row at the bottom of the range...?
    By strud in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2013, 08:53 AM
  7. How do I stop calculation??
    By jimojimo in forum Excel General
    Replies: 5
    Last Post: 04-16-2010, 12:03 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1