Forum Discussion

Sally350's avatar
Sally350
Copper Contributor
May 04, 2021
Solved

Ignoring cells with zero in formula range

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

Resources