Home

Blank cells show up as zeros in chart

%3CLINGO-SUB%20id%3D%22lingo-sub-806667%22%20slang%3D%22en-US%22%3EBlank%20cells%20show%20up%20as%20zeros%20in%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806667%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20taking%20simple%20averages%20of%20my%20data%20(e.g.%20%3DAVERAGE(B3%3AQ3))%20and%20then%20plotting%20them%20as%20a%20line%20in%20a%20scatter%20plot.%20However%2C%20there%20are%20many%20gaps%20in%20my%20data%2C%20so%20my%20averages%20often%20return%20%23DIV%2F0!.%20I'd%20like%20these%20values%20to%20show%20up%20as%20blanks%20in%20my%20graphs%2C%20but%20they're%20showing%20up%20as%20zeroes.%20I've%20also%20tried%20the%20formula%20%3DIFERROR(AVERAGE(B3%3AQ3)%2C%22%22)%20to%20change%20the%20%23DIV%2F0!%20cells%20to%20blanks%2C%20but%20it%20makes%20no%20difference%20in%20the%20chart.%20Additionally%2C%20I've%20gone%20into%20%22Select%20Data%22%20and%20made%20sure%20that%20%22Show%20empty%20cells%20as%3A%20Gaps%22%20is%20selected.%20Why%20are%20my%20blanks%20still%20showing%20up%20as%20zeroes%3F%20I'm%20using%20Excel%20on%20Mac.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-806667%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806858%22%20slang%3D%22en-US%22%3ERe%3A%20Blank%20cells%20show%20up%20as%20zeros%20in%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806858%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392901%22%20target%3D%22_blank%22%3E%40Emilia480%3C%2FA%3E%26nbsp%3Bto%20ignore%20what's%20happening%2C%20use%20this%3A%3C%2FP%3E%3CP%3E%3DIF(COUNTA(B3%3AQ3)%3D0%3B%22%22%3BAVERAGE(B3%3AQ3))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20way%2C%20IF%20there%20are%20no%3CSPAN%3E%26nbsp%3Bvalue%20(and%20so%20the%20average%20of%20no%20values%20will%20export%20%23DIV%2F0%20error)%20then%20leave%20blank%20(%22%22)%3B%20else%20(so%20if%20there%20are%20some%20values%2C%20positive%20or%20negative)%2C%20then%20apply%20AVERAGE.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EYou%20can%20do%20the%20same%20with%20SUM%20instead%20of%20COUNTA%2C%20but%20that%20means%20that%20a%20row%20with%20no%20values%20and%20a%20row%20with%20some%20value%20that%20summed%20are%200%20are%20going%20to%20be%20counted%20as%20the%20same.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWith%20COUNTA%2C%20a%20row%20with%20no%20values%20will%20be%20empty%20(%22%22)%20and%20a%20row%20with%20value%20that%20sums%200%20shows%20average%200%20as%20it%20should%20be.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807123%22%20slang%3D%22en-US%22%3ERe%3A%20Blank%20cells%20show%20up%20as%20zeros%20in%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F152529%22%20target%3D%22_blank%22%3E%40Arul%20Tresoldi%3C%2FA%3E%26nbsp%3Bthanks%20for%20you%20help.%20However%2C%20when%20I%20tried%20to%20use%20that%20formula%2C%20it%20says%20its%20not%20a%20valid%20formula.%20Any%20ideas%20why%20it's%20not%20working%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807124%22%20slang%3D%22en-US%22%3ERe%3A%20Blank%20cells%20show%20up%20as%20zeros%20in%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392901%22%20target%3D%22_blank%22%3E%40Emilia480%3C%2FA%3E%26nbsp%3B%2C%20try%20to%20change%20semicolons%20on%20commas%20within%20the%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807154%22%20slang%3D%22en-US%22%3ERe%3A%20Blank%20cells%20show%20up%20as%20zeros%20in%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807154%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F152529%22%20target%3D%22_blank%22%3E%40Arul%20Tresoldi%3C%2FA%3E%2C%20I%20replaced%20the%20semicolons%20with%20commas%2C%20which%20allowed%20the%20formula%20to%20go%20through.%20Thanks%20for%20the%20suggestion.%20But%20the%20blank%20cells%20still%20show%20up%20as%20zeros%20in%20my%20chart.%20Not%20sure%20what%20I'm%20missing.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807440%22%20slang%3D%22en-US%22%3ERe%3A%20Blank%20cells%20show%20up%20as%20zeros%20in%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807440%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392901%22%20target%3D%22_blank%22%3E%40Emilia480%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20your%20formula%20you%20may%20return%20%23N%2FA%20if%20error%2C%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(%3CYOUR%20formula%3D%22%22%3E%2CNA())%3C%2FYOUR%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20check%20Show%20%23N%2FA%20as%20empty%20cells%20together%20with%20Gaps%20for%20empty%20cells%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20501px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127198i6B28837212EFF3F1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENot%20to%20show%20errors%20in%20the%20table%20you%20may%20apply%20conditional%20formatting%20rule%20and%20format%20font%20to%20same%20color%20as%20background%20(white)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20485px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127199iB5B1AA176F334DE2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20find%20this%20sample%20in%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E
Emilia480
New Contributor

Hello,

 

I'm taking simple averages of my data (e.g. =AVERAGE(B3:Q3)) and then plotting them as a line in a scatter plot. However, there are many gaps in my data, so my averages often return #DIV/0!. I'd like these values to show up as blanks in my graphs, but they're showing up as zeroes. I've also tried the formula =IFERROR(AVERAGE(B3:Q3),"") to change the #DIV/0! cells to blanks, but it makes no difference in the chart. Additionally, I've gone into "Select Data" and made sure that "Show empty cells as: Gaps" is selected. Why are my blanks still showing up as zeroes? I'm using Excel on Mac.

 

Thanks

5 Replies

@Emilia480 to ignore what's happening, use this:

=IF(COUNTA(B3:Q3)=0;"";AVERAGE(B3:Q3))

 

This way, IF there are no value (and so the average of no values will export #DIV/0 error) then leave blank (""); else (so if there are some values, positive or negative), then apply AVERAGE.

 

You can do the same with SUM instead of COUNTA, but that means that a row with no values and a row with some value that summed are 0 are going to be counted as the same.

With COUNTA, a row with no values will be empty ("") and a row with value that sums 0 shows average 0 as it should be.

@Arul Tresoldi thanks for you help. However, when I tried to use that formula, it says its not a valid formula. Any ideas why it's not working?

@Emilia480 , try to change semicolons on commas within the formula

@Sergei Baklan @Arul Tresoldi, I replaced the semicolons with commas, which allowed the formula to go through. Thanks for the suggestion. But the blank cells still show up as zeros in my chart. Not sure what I'm missing.

@Emilia480 

For your formula you may return #N/A if error, like

=IFERROR(<your formula>,NA())

and check Show #N/A as empty cells together with Gaps for empty cells

image.png

Not to show errors in the table you may apply conditional formatting rule and format font to same color as background (white)

image.png

Please find this sample in attached file

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies