+ Reply to Thread
Results 1 to 4 of 4

Shorter formula

  1. #1
    Registered User
    Join Date
    03-10-2006
    Posts
    48

    Shorter formula

    D3 is the a cell holding a total, d12 threw d428 at every 8th interval to deduct from the total in d3.

    =D3-(D12-D20-D28-D36-D44-D52-D60-D68-D76-D84-D92-D100-D108-D116-D124-D132-D140-D148-D156-D164-D172-D180-D188-D196-D204-D212-D220-D228-D236-D244-D252-D260-D268-D276-D284-D292-D300-D308-D316-D324-D332-D340-D348-D356-D364-D372-D380-D388-D396-D404-D412-D420-D428)

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Take a look at this link and apply to a column

    http://exceltips.vitalnews.com/Pages..._in_a_Row.html
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653
    D3 is the a cell holding a total, d12 threw d428 at every 8th interval to deduct from the total in d3.
    Your formula is not consistent with your description; it should be either

    =D3-(D12+D20+D28+D36+D44+D52+D60+D68+D76+D84+D92+D100+D108+D116+D124+D132+D140+D148+D156+D164+D172+D180+D188+D196+D204+D212+D220+D228+D236+D244+D252+D260+D268+D276+D284+D292+D300+D308+D316+D324+D332+D340+D348+D356+D364+D372+D380+D388+D396+D404+D412+D420+D428)

    or

    =D3-D12-D20-D28-D36-D44-D52-D60-D68-D76-D84-D92-D100-D108-D116-D124-D132-D140-D148-D156-D164-D172-D180-D188-D196-D204-D212-D220-D228-D236-D244-D252-D260-D268-D276-D284-D292-D300-D308-D316-D324-D332-D340-D348-D356-D364-D372-D380-D388-D396-D404-D412-D420-D428

    Assuming that's so, then

    =D3 - SUMPRODUCT( (MOD( ROW(D12:D428) - ROW(D12), 8) = 0 ) * D12:D428)

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Is there any indication in any other cells which cells to sum? E.g. if C12, C20, C28 etc. say "Total" you could use a formula like:

    =D3-SUMIF(C12:C428,"Total",D12:D428)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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