Home

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

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