SOLVED

Formula Question

%3CLINGO-SUB%20id%3D%22lingo-sub-2871432%22%20slang%3D%22en-US%22%3EFormula%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2871432%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20figure%20out%20why%20this%20formula%20is%20not%20working%20on%20this%20sheet.%3C%2FP%3E%3CP%3E%3Dsum(small(y4%3Ay8%2Crow(indirect(%221.4%22))))%3C%2FP%3E%3CP%3EI%20need%20to%20get%20the%20formula%20to%20work%20on%20this%20template.%20Or%20am%20I%20completely%20wrong%20on%20the%20formula%3F%26nbsp%3B%20Any%20help%20is%20greatly%20appreciated!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22295%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22295%22%3E10%20Best%20Lowest%20Net%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%20Best%20Putt%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%20Lowest%20Net%20on%20Low%20Net%20Day%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2871432%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2872035%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2872035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1192441%22%20target%3D%22_blank%22%3E%40pstover%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDepends%20on%20Excel%20version%2C%20idea%20of%20the%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUM(%20SMALL(%20FILTER(D4%3AD24%2C%20D4%3AD24%20%26gt%3B%200%20)%2C%20SEQUENCE(10)%20)%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ELogic%20is%20also%20not%20clear%2C%20at%20least%20for%20me%20-%20what%20is%20Low%20Net%20Day%2C%20shall%20we%20calculate%20requested%20by%20column%2C%20by%20front%20and%20back%2C%20by%20entire%20data%20set.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2872037%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2872037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1192441%22%20target%3D%22_blank%22%3E%40pstover%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EForgot%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2874481%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2874481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20C28%20I%20used%26nbsp%3B%3DAVERAGEIF(Y4%3AY24%2C%22%26lt%3B%26gt%3B0%22)%20to%20not%20include%20the%200%20in%20the%20average%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20is%20to%20only%20count%20the%2010%20lowest%20scores.%26nbsp%3B%20See%20formula%20below.%26nbsp%3B%20Do%20you%20know%20the%20formula%20is%20to%20exclude%20zero's%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(SMALL(Y4%3AY24%2CROW(INDIRECT(%221%3A10%22))))%26nbsp%3B%20Cell%20AC2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

I am trying to figure out why this formula is not working on this sheet.

=sum(small(y4:y8,row(indirect("1.4"))))

I need to get the formula to work on this template. Or am I completely wrong on the formula?  Any help is greatly appreciated!!!

 

10 Best Lowest Net
10 Best Putt
4 Lowest Net on Low Net Day

 

 

6 Replies

@pstover 

Depends on Excel version, idea of the formula could be

=SUM( SMALL( FILTER(D4:D24, D4:D24 > 0 ), SEQUENCE(10) ) )

Logic is also not clear, at least for me - what is Low Net Day, shall we calculate requested by column, by front and back, by entire data set. 

@pstover 

Forgot file

@Sergei Baklan 

Good morning,

 

Thank you for your reply. 

On C28 I used =AVERAGEIF(Y4:Y24,"<>0") to not include the 0 in the average

 

The formula is to only count the 10 lowest scores.  See formula below.  Do you know the formula is to exclude zero's? 

=SUM(SMALL(Y4:Y24,ROW(INDIRECT("1:10"))))  Cell AC2

 

I appreciate your help!

 

 

 

best response confirmed by allyreckerman (Microsoft)
Solution

@pstover 

For your variant of formula that could be

=SUM( SMALL( Y4:Y24, COUNTIF( Y4:Y24, 0 ) + ROW( INDIRECT( "1:10" ) ) ) )

 

@Sergei Baklan 

 

Thank you so much!  This works perfect!

Have an amazing day Sergei.

 

All the best,

 

Peg

@pstover , thank you, you too.