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

Related Conversations
Teams no longer auto launches
trandall in Microsoft Teams on
4 Replies
Creating a form with calculations
HimanshuS87 in Microsoft Forms on
1 Replies