SOLVED

Formula Question

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

1 best response

Accepted Solutions
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" ) ) ) )

 

View solution in original post