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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies