SOLVED

Calculated value(s) not displayed in the table, only ##### appear

%3CLINGO-SUB%20id%3D%22lingo-sub-3177779%22%20slang%3D%22en-US%22%3ECalculated%20value(s)%20not%20displayed%20in%20the%20table%2C%20only%20%23%23%23%23%23%20appear%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3177779%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Excel%20community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20prepared%20a%20spreadsheet%20calculator%20of%20sorts%20using%20Excel%2C%20which%20is%20attached%20to%20this%20message.%3C%2FP%3E%3CP%3EIn%20the%20initial%20tables%20displaying%20Sub-Totals%20(A)%20and%20Sub-Totals%20(B)%2C%20the%20calculated%20values%20are%20not%20displayed.%26nbsp%3B%20Instead%20%23%23%23%20can%20be%20seen%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sub-total-A.PNG%22%20style%3D%22width%3A%20640px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349018i98E00949EE9B927D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Sub-total-A.PNG%22%20alt%3D%22Sub-total-A.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sub-total-D.PNG%22%20style%3D%22width%3A%20648px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349020iEEE8C63F818D66AA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Sub-total-D.PNG%22%20alt%3D%22Sub-total-D.PNG%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20like%20to%20check%20if%20this%20is%20a%20bug%20in%20the%20spreadsheet%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20and%20Regards%2C%3C%2FP%3E%3CP%3EBenjamin%20Wong%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3177779%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3177804%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20value(s)%20not%20displayed%20in%20the%20table%2C%20only%20%23%23%23%23%23%20appear%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3177804%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F511322%22%20target%3D%22_blank%22%3E%40ckwongb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fchange-the-column-width-and-row-height-72f5e3cc-994d-43e8-ae58-9774a0905f46%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EChange%20the%20column%20width%20and%20row%20height%3C%2FA%3E%3C%2FP%3E%3CP%3EDo%20one%20of%20the%20following%3A%3C%2FP%3E%3CP%3ETo%20change%20the%20width%20of%20one%20column%2C%20drag%20the%20boundary%20on%20the%20right%20side%20of%20the%20column%20heading%20until%20the%20column%20is%20the%20width%20that%20you%20want%20or%20double%20click%20on%20the%20right%20side%20with%20the%20mouse.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20could%20help%20you%20with%20these%20information%20%2F%20links.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3177916%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20value(s)%20not%20displayed%20in%20the%20table%2C%20only%20%23%23%23%23%23%20appear%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3177916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F511322%22%20target%3D%22_blank%22%3E%40ckwongb%3C%2FA%3E%26nbsp%3BIt's%20not%20a%20bug.%20It's%20due%20to%20the%20fact%20that%20you%20have%20used%20merged%20cells%20in%20columns%20J%3AK%20and%20L%3AM.%20It%20looks%20like%20you%20have%20the%20sum%20formulae%20in%20the%20wider%20columns%20K%20and%20M%2C%20but%20they%20are%20in%20fact%20in%20the%20very%20narrow%20columns%20J%20and%20L.%20Both%20too%20narrow%20to%20display%20the%20result.%20Follow%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%26nbsp%3Binstructions%20to%20widen%20columns%20J%20and%20L%2C%20but%20better%20to%20get%20rid%20of%20the%20merged%20cells%20as%20the%20only%20create%20problems.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3182155%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20value(s)%20not%20displayed%20in%20the%20table%2C%20only%20%23%23%23%23%23%20appear%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3182155%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20advised%2C%20have%20adjusted%20the%20column%20widths%20for%20columns%20J%20and%20L%2C%20but%20the%20value%20is%20still%20not%20displayed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20the%20value%20appears%20in%20%3CA%20href%3D%22https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1AggCOAklxMm2JDBD9yDZtc-LsmniArTV%2Fedit%3Fusp%3Dsharing%26amp%3Bouid%3D100229931900988909555%26amp%3Brtpof%3Dtrue%26amp%3Bsd%3Dtrue%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EGoogle%20Sheets%3C%2FA%3E%2C%20which%20is%20strange.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Benjamin%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Dear Excel community,

 

Have prepared a spreadsheet calculator of sorts using Excel, which is attached to this message here.

 

In the initial tables displaying Sub-Totals (A) and Sub-Totals (D), the calculated values are not displayed.  Instead ### can be seen:

 

Sub-total-A.PNGSub-total-D.PNG  

 

Would like to check if this is a bug in the spreadsheet?

 

Thank you and Regards,

Benjamin Wong

 

 

6 Replies
best response confirmed by ckwongb (New Contributor)
Solution

@ckwongb 

Change the column width and row height

Do one of the following:

To change the width of one column, drag the boundary on the right side of the column heading until the column is the width that you want or double click on the right side with the mouse.

 

Hope I could help you with these information / links.

 

NikolinoDE

I know I don't know anything (Socrates)

@ckwongb It's not a bug. It's due to the fact that you have used merged cells in columns J:K and L:M. It looks like you have the sum formulae in the wider columns K and M, but they are in fact in the very narrow columns J and L. Both too narrow to display the result. Follow @NikolinoDE instructions to widen columns J and L, but better to get rid of the merged cells as the only create problems.

@Riny_van_Eekelen 

 

As advised, have adjusted the column widths for columns J and L, but the value is still not displayed.

 

However, the value appears in Google Sheets, which is strange.

 

- Benjamin

@NikolinoDE 

Thanks for your help via direct messaging.

It would be a good feature to allow attachment of file(s) / embed feature in the discussion forum (: 

@ckwongb 

Another issue with your spreadsheet is that you formatted the alignment as left with indent 29 - which puts the value off the right edge of the cell.

In fact your subtotal cells have different indents.

Such inconsistent formatting usually comes from a converted PDF.

 

@ckwongb 

Yes, it's about time.