+ Reply to Thread
Results 1 to 3 of 3

very difficult sumproduct formula. SO SO DIFFICULT!! HELP ME!!!!!

  1. #1
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    very difficult sumproduct formula. SO SO DIFFICULT!! HELP ME!!!!!

    Hello. I was given the formula below and asked if someone could explain what it was doing. I tried to use the evaluate formula but I didn't understand it. Could someone break it down piece by piece for me? Also, could someone adjust it so that it makes row 6 (starting in C6) equal to the values in row 50 in the attached spreadsheet. The formula does the right calculation (on row 5) but when I copied it from the example it doesn't work on the new sheet. Thanks.

    =SUMPRODUCT(1-COLUMN($A3:INDEX($A3:A3,MIN(12,COLUMN(A3))))*8.3333%,N(OFFSET($A2,,COLUMNS($A3:A3)-COLUMN($A3:INDEX($A3:A3,MIN(12,COLUMN(A3)))))))
    Attached Files Attached Files

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    953

    Re: very difficult sumproduct formula. SO SO DIFFICULT!! HELP ME!!!!!

    Hi amartino44,

    Can you tell me which Cell this SumProduct Formula appears in on this file?

    Thanks
    Remember you are unique, like everyone else

  3. #3
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: very difficult sumproduct formula. SO SO DIFFICULT!! HELP ME!!!!!

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I have adjusted the formula so that it works. Just paste it into C6 and copy across.

    The formula was not working because it was not designed for the layout you had. I suspect it originally didn't start at row 4. The only real change is $A2 to $A5 (although I have changed a few other references, but they don't really affect the formula, they are being used as counters. (Column(A1) = 1 etc).

    I would recommend one change - instead of having 8.3333%, replace it with $C$18.

    It is difficult to explain how it works, but whoever designed the formula is very clever!

    The first part: 1-COLUMN($A6:INDEX($A6:C6,MIN(12,COLUMN(C6))))*8.3333% is building an array of Earned Rate per column. In the first column it is 1 -.08333 = .91667. In the next column, it is 1 - .08333 -.08333 = .83334 and so on.

    The second half N(OFFSET($A5,,COLUMNS($A1:C1)-COLUMN($A1:INDEX($A1:C1,MIN(12,COLUMN(C1))))))is building another array. The N forces a number - 0 if a "non-number" value is returned. The OFFSET part is putting the values in row 5 in reverse order. These two arrays are then multiplied together, and summed.

    I hope this helps a little.

    David



    When you reply please make it clear WHO you are responding to by mentioning their name.
    If we have been of assistance, please let us know. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.
    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

+ 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. Very Difficult Sumproduct formula
    By amartino44 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2013, 04:48 PM
  2. Replies: 6
    Last Post: 03-19-2013, 11:41 AM
  3. Difficult formula SUMPRODUCT,MATCH,WEEKDAY
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-08-2006, 06:53 AM
  4. Difficult Formula
    By Cindy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-09-2006, 06:30 PM
  5. Difficult look up formula
    By Alex in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2005, 05:05 PM

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