Ask Your Question

Why am I getting ### in some of my cells?

asked 2018-09-17 00:49:53 +0100

PKB_68 gravatar image

I am trying to do simple calculations in the spreadsheet. The grid I am using is currently in the border of AH horizontal and 30 vertical the cells consist of simple whole numbers. In my equation cell (AJ) I type in the following formula; =SUM(C2+E2+F2+I2+K2+L2+N2+P2+U2+Y2) and I then copy that formula down the column to line 30 but when I hit enter I am only getting numbers in about half of the cells. The other cells only show ###. The column is obviously wide enough because it is showing answers in some of the cells. I have tried manually entering the formula in each and every cell that shows ### but that does not work either. I am just not sure what I am doing wrong. Any help would be appreciated. Thanks.

edit retag flag offensive close merge delete


Your logic is faulty. Just because some results are of a size to fit in the width of a column doesn't mean ALL results are of that size or smaller. Widen the column!

Ratslinger gravatar imageRatslinger ( 2018-09-17 01:29:32 +0100 )edit

Yes I understand your pov but the numbers are all currently of the same width (only 2 characters wide) but I did change the width just to see and the problem still exists but it has changed. Now instead of showing ### it is showing #VALUE in the cells. Thank you for your input and if you can think of anything else let me know.

PKB_68 gravatar imagePKB_68 ( 2018-09-17 03:17:18 +0100 )edit

Verify the contents of the cells within the sum where you are getting the error. You probably have a non numeric character within.

Ratslinger gravatar imageRatslinger ( 2018-09-17 03:31:28 +0100 )edit

Yeah I thought about that as well. The cell contents are either all simple whole numbers or blank cells.

PKB_68 gravatar imagePKB_68 ( 2018-09-17 03:59:02 +0100 )edit

At this point it appears only a posted sample may help.

Ratslinger gravatar imageRatslinger ( 2018-09-17 04:14:16 +0100 )edit

Did you try to print out? Sometimes, Calc shows hash symbols when it can't properly render numbers on the screen, but a printout is fine. In such a case, either zoom in or try changing the font.

gabix gravatar imagegabix ( 2018-09-17 14:07:49 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-09-17 02:02:34 +0100

appreciatethehelp gravatar image

I second Ratslinger.

The only time I have come across "###" is when there isn't enough space to display the characters in the cell.

I know you're working with "simple whole numbers", but FYI If you don't want the characters to be any smaller but still need them to fit inside the column without widening it you can always either make the text size smaller or adjust the amount of decimal places (Format > Cells > Numbers tab Options section).

edit flag offensive delete link more


Thanks for your input and did change the width as well as making sure that I have the cells formatted to not show decimal places and as I mentioned above the problem still exists but it has changed a bit. It is now showing #VALUE instead of ###. If you can think of anything else I will gladly try it. Thank you.

PKB_68 gravatar imagePKB_68 ( 2018-09-17 03:19:18 +0100 )edit

@PKB_68, if it shows #VALUE, it means the calculation in that cell could not be completed, e.g you tried to calculate with text cells (or empty string cells ="").

mahfiaz gravatar imagemahfiaz ( 2018-09-17 06:59:37 +0100 )edit

@mahfiaz What is a "string cell"?

appreciatethehelp gravatar imageappreciatethehelp ( 2018-09-25 13:36:50 +0100 )edit

It's a cell what contains a string, the string could be empty as well. Click on cell, type ="" and press Enter.

mahfiaz gravatar imagemahfiaz ( 2018-09-26 11:37:16 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-17 00:49:53 +0100

Seen: 35 times

Last updated: Sep 17 '18