Auto filling fields and calculations

%3CLINGO-SUB%20id%3D%22lingo-sub-679599%22%20slang%3D%22en-US%22%3EAuto%20filling%20fields%20and%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679599%22%20slang%3D%22en-US%22%3E%3CP%3EHere's%20my%20problem.%26nbsp%3B%20I%20have%20a%20poker%20spreadsheet%20for%20let's%20say%2030%20people.%26nbsp%3B%20Spreadsheet%20shows%20how%20each%20person%20finished%20each%20month%20and%20the%20points%20awarded%20for%20each%20month%20and%20then%20automatically%20adds%20up%20each%20months%20points%20for%20a%20overall%20point%20total.%26nbsp%3B%20Can%20I%20add%20to%20the%20formula%20and%20ask%20to%20SUBTRACT%20the%20LOWEST%202%20months%20out%20of%20the%2012%20months%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-679599%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679659%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20filling%20fields%20and%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679659%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357211%22%20target%3D%22_blank%22%3E%40RedIce%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(%0A%20SUMPRODUCT(%24B2%3A%24Y2*(MOD(COLUMN(%24B%242%3A%24Y%242)%2C2)%3D1))-%0A%20SUM(AGGREGATE(15%2C6%2C1%2F(MOD(COLUMN(%24B%242%3A%24Y%242)%2C2)%3D1)%2F(LEN(%24B2%3A%24Y2)%26gt%3B0)*%24B2%3A%24Y2%2C%7B1%2C2%7D))%0A%2C0)%3C%2FPRE%3E%0A%3CP%3EIt%20ignores%20blank%20cells%20but%20takes%20into%20account%20cells%20with%200%2C%20and%20return%20zero%20if%20you%20have%20only%20one%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Here's my problem.  I have a poker spreadsheet for let's say 30 people.  Spreadsheet shows how each person finished each month and the points awarded for each month and then automatically adds up each months points for a overall point total.  Can I add to the formula and ask to SUBTRACT the LOWEST 2 months out of the 12 months?

1 Reply
Highlighted

@RedIce ,

That could be

=IFERROR(
 SUMPRODUCT($B2:$Y2*(MOD(COLUMN($B$2:$Y$2),2)=1))-
 SUM(AGGREGATE(15,6,1/(MOD(COLUMN($B$2:$Y$2),2)=1)/(LEN($B2:$Y2)>0)*$B2:$Y2,{1,2}))
,0)

It ignores blank cells but takes into account cells with 0, and return zero if you have only one result.