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

1. ## 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)))))))

2. ## 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

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

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.

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

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