[Solved] Error code ### from SMALL

[TheOldThug]

Hello
I am working on a golfsheet trying to add the smallest 1,2,or 3 scores, depending on the hole. When there are no numbers in the cells I get ### in the cell with the formula =SMALL(D17:D20;1)+SMALL(D17:D20;2)+SMALL(D17:D20;3). Once the scores are put in the cells it works properly. Is there some way to make the cell not display ### but rather 0 when no data is in the Cells. This works on my other spreadsheet that has the formula =SUM(B11:B14)-MAX(B11:B14).

Thank you
Windows 10

[MrProgrammer]

When there are no numbers in the cells I get ### in the cell with the formula =SMALL(D17:D20;1)+SMALL(D17:D20;2)+SMALL(D17:D20;3). Once the scores are put in the cells it works properly. Is there some way to make the cell not display ### but rather 0 when no data is in the Cells.

### probably means that the cell is not wide enough to display the error message. I don't understand if no data is in the cells means some of the cells are empty or all of the cells are empty. Try =IF(COUNT(D17:D20)>0;SMALL(D17:D20;1)+SMALL(D17:D20;2)+SMALL(D17:D20;3);0) or =IF(COUNT(D17:D20)>2;SMALL(D17:D20;1)+SMALL(D17:D20;2)+SMALL(D17:D20;3);0).

This works on my other spreadsheet that has the formula =SUM(B11:B14)-MAX(B11:B14).

If =SUM(…)-MAX(…) is working in another spreadsheet why don't you use that method in this one? It's easier to type than your SMALL() formula. SUM-MAX is what I would use to find the total of four cells, but exclude the largest value.

If you need any additional assistance attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach. Explain which cell in which sheet does not give the result you expect. Explain what the desired result is and why.

Your duplicate topic in the Beginners forum was deleted. Please don't multi-post.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[TheOldThug]

Thank you so much, you original idea with Count worked great. I could not use sum-max again because its a different format for a team game. The original was best 2 out of 3. or best 3 out of 4 scores depending how many played. This new format is best low score, 2 best low scores, 3 best low scores... repeat. I am curious why =SUM(B11:B14)-MAX(B11:B14) produced a 0 when no scores were entered yet while =SMALL(D17:D20;1)+SMALL(D17:D20;2)+SMALL(D17:D20;3) produced ### which same as #Value, when no scores entered yet. Not sure why error code with one and not the other.
Thx again

[RoryOF]

Frequently when there are different results such as you indicate, these are caused by incorrect data types.

/View /Value Highlighting shows the differing types of data: Black is Text, Blue is number, Green is formula, no matter how their display is formatted.

[TheOldThug]

Thx, that's good to know.