Determine LARGE with most recent data point excluded

Hello All,

First post here but have been a long-time lurker. I have an interesting Excel issue that has me stumped and I'm hoping you fine folks might be able to come up with a solution. I will throw down the gauntlet and say that I'm very proficient at Excel and typically am offering help to others but this one has me scratching my head. If you can solve this, you are definitely a worthy Excel user!!!

Here is my data as laid out in the spreadsheet

Col_A_____Col_B______Col_C_______Col_D_______Col_E______Col_F
Most Recent Event Date: 06/01/10
01/1/10____02/1/10____03/1/10______04/1/10_____05/01/10____06/01/10
355_______420________405_________257_________330________415

I am using the following formula to summate the top 4 of 6 available values currently:
=SUM(LARGE(($A2,$B2,$C2,$D2,$E2,$F2),{1,2,3,4}))

What I need to do is find the sum of the largest 4 values and exclude the value that matches the "Most Recent Event Date" of 06/01/10. The Most Recent Event Date will of course change as new data events are added with new dates, so if a match occurs between the Most Recent Event Date and the column Event Date, then that data entry should not be included in the LARGE function.

My use for the spreadsheet is to enter monthly data after is occurs (think sports scoring), and want to determine the total score amongst all participants prior to the last sporting event (thus the reason for excluding the last Event Date). This will allow me to calculate a historical ranking and then compare the historical rank to the most recent rank to see which competitors increased or decreased in rank after the newest scores are posted.

I told you this was a doozy. I would appreciate any help you folks can provide. Feel free to reply with any questions and I would be happy to further clarify and troubleshoot.

Is that meant to be C8 or should it be A2?

If one of the dates matches then do you exclude that column's value and then still take the largest 4 from the remaining 5?

Given the events are in chronological order. Does this work for you:

Dates in row 2 & Values In row 3

EDIT:

Correction:
=IF(COUNT(A3:IV3)<5;SUM(A3:IV3);SUM(LARGE(A3:INDEX(A3:IV3;0;MATCH(MAX(A2:IV2);A2:IV2;0)-1);{1;2;3;4})))

In case you have more than 6 columns of data will look at the highest 4 scores from the previous period back.

That is getting very close. I've tested that within my sheet and it gets close but I have a few more equation constraints that cause it not to work.

I've put together a sample test sheet here with a data layout that is close to what I'm using in my original model. The highlighted portion is the formula that I need. The other data is laid out and has constraints similar to my original spreadsheet (which is massive and has some sensitive data so don't want to simply attach it).

And for reference (since I know this will come up), in the actual sheet I am looking for the Top 8 of 12 entries (we have an event each month). Sorry to confuse that, I was trying to simplify the problem earlier but it didn't provide the best info in retrospect.

I've also scanned this to ensure that it is virus-free, so no issues. I greatly appreciate your help!

Please see attached. You would make it a lot easier for yourself without the spaces between the data columns.

pb71's suggested formula seems to work even if you have data in the intermediate cells like F3 and H3. It fails if Event count is 8 or less....but you could fix that with a small tweak like this

=IF(D3-1<8,SUM(IF(MOD(COLUMN(E3:Q3),2),E3:Q3)),SUM(LARGE(IF(ISNUMBER(E3:AA3)*MOD(COLUMN(E3:AA3),2)*($E$2:$AA$2<$A$1),E3:AA3),{1,2,3,4,5,6,7,8})))

Personally I think you could avoid the MOD(COLUMN usage if you can differentiate between column headers, e.g. as long as row 2 will have numbers (dates) in the relevant columns only then you could simplify to

=SUM(IF(D3-1<8,IF(ISNUMBER(E$2:AA$2),E3:AA3),LARGE(IF(ISNUMBER(E$2:AA$2)*(E$2:AA$2<$A$1),E3:AA3), {1,2,3,4,5,6,7,8})))

confirmed with CTRL+SHIFT+ENTER

Hello Matt,

I agree with pb71, I had trouble opening your sheet, best to post a version on here. I'm not sure what the issue is, pb71's suggestion will ignore the intermediate columns.......is it that they aren't every other column....are they evenly spaced?

pb71,

Don't believe you need ISNUMBER in any version [of excel], shorter version works fine....

regards, daddylonglegs

Hello Matt,

I think you can use the method I proposed previously, with a small tweak. The columns you want to consider all have "Combined" in row 6 so you can use this formula in B7

=SUM(LARGE(IF((V$6:CJ$6="Combined")*(T$5:CH$5<$A$3),V7:CJ7,0),{1,2,3,4,5,6,7,8}))

confirmed with CTRL+SHIFT+ENTER and copied down.....and given that non-qualifying columns show zero you don't need to make any special provision for less than 8 values - that will be accommodated automatically

Ok, not a problem. I'm using Excel 2010 so the file is saved as an .xlsx format which is probably the issue. I'll save it as an .xls for you all.

Attached is the original sheet with all the sensitive stuff removed. I've highlighted the data relevant to the formula in red. I also highlighted the "Prior Top 8 Score" which is where the formula result will reside.

@pb71, regarding the <8 results, if a competitor has less than 8 results, they will simply be summed and entered except for the most recent match. I was planning on an IF statement to have the simple summation sans last match result if TRUE, the complex summation of the top 8 minus last event results if FALSE.

To keep the formula objective in perspective, my output is simply to remove the last score as of the last event date (the one that matches the date entered in the "Last Data Update" pulldown). If the date there matches the event date, then that month's points should be removed. This result sans the last months points, will then be ranked and used to calculate the difference between the current rank and the old rank.

Being able to see how competitors move up and down the ranks each month is what we're trying to do!

pb71, thanks for that, it worked. At first I forgot to use the cntr+shift+enter to enable it but after that, the data came up!

I'll give it some tests today but I think everything looks great! Thanks for all the help!

------------------------------------------------------

Well Daddylonglegs and pb71, your fixes have allowed me to use a date selection to provide the functionality I need. Thanks.

I have one last question though. In the spreadsheet in post #17 above, I have one column that is not date adjusting and can't seem to get the array functionality to work correctly. I am trying to have the L column (# of matches) also adjust using a similar date array function. For example, if I choose June, the result would display "6" if the competitor participated in the first six matches.

My idea was to create an array using the "combined" field again (columns V,AB,AH,AN,AT,AZ,BF,BL,BR,BX,**,CJ) and the COUNTIF function to count the number of times a person competes if the "combined" result is less than or greater than 0. If a positive or negative number is in the "combined" column, it would count it according to which date was selected in the pulldown match date (cell: A3).

Can you help me construct this formula?

Hello Matt,

You could try SUMPRODUCT like this

=SUMPRODUCT((V$6:CJ$6="Combined")*(T$5:CH$5<=$A$3)*(V7:CJ7<>0))

or, since you have Excel 2010 you could also try COUNTIFS for the same result, i.e.

=COUNTIFS(V$6:CJ$6,"Combined",T$5:CH$5,"<="&$A$3,V7:CJ7,"<>0")

COUNTIFS is in Excel 2007 and later only so if you want to share with users who have Excel 2003 or earlier SUMPRODUCT is better.

COUNTIFS is more efficient but unless you have thousands of rows of data you probably won't notice much difference