SOLVED

Ignoring cells with zero in formula range

%3CLINGO-SUB%20id%3D%22lingo-sub-2322416%22%20slang%3D%22en-US%22%3EIgnoring%20cells%20with%20zero%20in%20formula%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2322416%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20spreadsheet%20to%20calculate%20golf%20handicaps.%20I%20am%20using%20the%20small%20function%20to%20calculate%20differentials.%20There%20can%20be%201%20to%2020%20entries.%20When%20I%20have%20less%20than%2020%20entries%20the%20formula%20picks%20up%20the%20zero%20cells%20in%20the%20formula.%20The%20formula%20I%20am%20using%20is%3A%26nbsp%3B%20%3D%20SUMPRODUCT(SMALL(F6%3AF25%2CROW(DIRECT(%221%3A%22%26amp%3BF27))).%20Cell%20F28.%20There%20is%20a%20tab%20that%20returns%20the%20number%20of%20SMALL%20entries%20to%20be%20used%20in%20the%20calculation%2C%20USGA%20tab.%20So%20if%20I%20only%20have%2011%20entries%20and%20I%20want%20to%20use%20the%203%20smallest%20answers%20to%20generate%20my%20answer%20I%20have%209%20rows%20with%20zero%20that%20get%20picked%20up%20in%20the%20formula.%26nbsp%3B%20For%2011%20entries%20I%20need%20to%20change%20the%20range%20to%20SMALL(F6%3AF16).%20Not%20to%20F25.%20How%20can%20I%20ignore%20the%20rows%20that%20do%20not%20have%20values%20in%20them%20as%20yet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2322416%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2322475%22%20slang%3D%22en-US%22%3ERe%3A%20Ignoring%20cells%20with%20zero%20in%20formula%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2322475%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044837%22%20target%3D%22_blank%22%3E%40Sally350%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(SMALL(F6%3AINDEX(F6%3AF25%2CF26)%2CROW(INDIRECT(%221%3A%22%20%26amp%3B%20F27))))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am working on a spreadsheet to calculate golf handicaps. I am using the small function to calculate differentials. There can be 1 to 20 entries. When I have less than 20 entries the formula picks up the zero cells in the formula. The formula I am using is:  = SUMPRODUCT(SMALL(F6:F25,ROW(DIRECT("1:"&F27))). Cell F28. There is a tab that returns the number of SMALL entries to be used in the calculation, USGA tab. So if I only have 11 entries and I want to use the 3 smallest answers to generate my answer I have 9 rows with zero that get picked up in the formula.  For 11 entries I need to change the range to SMALL(F6:F16). Not to F25. How can I ignore the rows that do not have values in them as yet?

1 Reply
best response confirmed by Sally350 (Occasional Visitor)
Solution

@Sally350 

Use

=SUMPRODUCT(SMALL(F6:INDEX(F6:F25,F26),ROW(INDIRECT("1:" & F27))))