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)))))))

Formula:

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".