SOLVED

Scatter chart plots wrongly if blank cells exist in input range

%3CLINGO-SUB%20id%3D%22lingo-sub-1337190%22%20slang%3D%22en-US%22%3EScatter%20chart%20plots%20wrongly%20if%20blank%20cells%20exist%20in%20input%20range%2C%20and%20u%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337190%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20make%20a%20general%20template%20to%20plot%20a%20Q-Q%20plot%20for%20upto%20100%20data%20points%20(if%20a%20reader%20is%20unaware%20of%20what%20a%20Q-Q%20plot%20is%2C%20in%20present%20context%20it%20doesn't%20matter%2C%20just%20take%20it%20as%20a%20simple%20X-Y%20Scatter%20Chart)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%20being%20faced%20is%20that%20if%20there%20is%20even%20one%20x-y%20pair%20empty%20in%20the%20input%20cells%20range%20of%20that%20scatter%20chart%2C%20then%20the%20chart%20doen't%20plot%20correctly.%20Instead%20of%20plotting%20selected%20y%20against%20selected%20x%20alues%2C%20it%20just%20plots%20the%20selected%20y%20valyes%20against%20serial%20number%20of%20the%20ys%20i.e.%20x-range%20just%20becomes%201%2C2%2C3%2C...always%20%3CEM%3Eirrespective%20of%3C%2FEM%3E%20what%20values%20are%20there%20in%20the%20designated%20column%20of%20x-values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESo%20my%20problem%20is%2C%20since%20scatter%20chart%20is%26nbsp%3B%3C%2FSTRONG%3E%3CSTRONG%3Eunable%20to%20take%20a%20blank%20cell%20as%20input%2C%26nbsp%3B%3C%2FSTRONG%3E%3CSTRONG%3Eso%20i%20am%20not%20able%20to%20make%20a%20generic%26nbsp%3B%3C%2FSTRONG%3E%3CSTRONG%3Etemplate%20to%3C%2FSTRONG%3E%3CSTRONG%3E%20make%20a%20Q-Q%20Plot.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20said%20file%20is%20attached%20-%20there%20are%202%20sheets%20in%20it%20-%20the%20sheet%20%3CSTRONG%3E100%20points%3C%2FSTRONG%3E%20with%20all%20100%20x-y%20pairs%20filled%20plots%20the%20correct%20chart%20-%20no%20problems%2C%20and%20in%20a%20duplicated%20copy%20of%20same%20sheet%20titled%20%3CSTRONG%3E99%20points%3C%2FSTRONG%3E%26nbsp%3Bthe%20last%20x-y%20pair%20cells%20were%20made%20blank%2C%20and%20doing%20just%20this%20destroys%20the%20whole%20x-y%20scatter!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20seek%20a%20solution%20or%20workaround%20to%20this%20problem!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1337190%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1337256%22%20slang%3D%22en-US%22%3ERe%3A%20Scatter%20chart%20plots%20wrongly%20if%20blank%20cells%20exist%20in%20input%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636579%22%20target%3D%22_blank%22%3E%40amit_bhola%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20dynamic%20ranges%20using%20named%20formulas%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20544px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F186602i6564A52DC1721D25%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eusing%20them%20as%20data%20for%20chart%20series%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20435px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F186604i00867F20258B01FD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EDon't%20forget%20sheet%20name%20applying%20series%20values%2C%20like%20%3D'99%20points'!PlotX%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1337443%22%20slang%3D%22en-US%22%3ERe%3A%20Scatter%20chart%20plots%20wrongly%20if%20blank%20cells%20exist%20in%20input%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337443%22%20slang%3D%22en-US%22%3E%3CP%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%26nbsp%3Bthanks!%20i%20get%20the%20idea%20and%20it%20seems%20a%20good%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStrangely%20though%2C%20in%20my%20version%20of%20Excel%20(2016)%2C%20the%20named%20formula%20is%20automatically%20referring%20to%20the%20workbook%20name.%20Even%20when%20i%20open%20your%20(downloaded)%20solution%20workbook%20as%20it%20is%2C%20it%20shows%20workbook%20name%20instead%20of%20sheet%20name.%20Must%20be%20something%20to%20do%20with%20the%20excel%20version%20i%20guess.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20thanks%2C%20i%20get%20the%20line%20of%20the%20solution!%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%22amit_bhola_0-1587814761764.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F186629i6A5B84580F08ADAF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22amit_bhola_0-1587814761764.png%22%20alt%3D%22amit_bhola_0-1587814761764.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I am trying to make a general template to plot a Q-Q plot for upto 100 data points (if a reader is unaware of what a Q-Q plot is, in present context it doesn't matter, just take it as a simple X-Y Scatter Chart)

 

Problem being faced is that if there is even one x-y pair empty in the input cells range of that scatter chart, then the chart doen't plot correctly. Instead of plotting selected y against selected x alues, it just plots the selected y valyes against serial number of the ys i.e. x-range just becomes 1,2,3,...always irrespective of what values are there in the designated column of x-values.

 

So my problem is, since scatter chart is unable to take a blank cell as input, so i am not able to make a generic template to make a Q-Q Plot.

 

The said file is attached - there are 2 sheets in it - the sheet 100 points with all 100 x-y pairs filled plots the correct chart - no problems, and in a duplicated copy of same sheet titled 99 points the last x-y pair cells were made blank, and doing just this destroys the whole x-y scatter!

 

I seek a solution or workaround to this problem!

2 Replies
Highlighted
Best Response confirmed by amit_bhola (Contributor)
Solution

@amit_bhola 

You may apply dynamic ranges using named formulas as

image.png

using them as data for chart series

image.png

Don't forget sheet name applying series values, like ='99 points'!PlotX

Highlighted

@Sergei Baklan thanks! i get the idea and it seems a good solution.

 

Strangely though, in my version of Excel (2016), the named formula is automatically referring to the workbook name. Even when i open your (downloaded) solution workbook as it is, it shows workbook name instead of sheet name. Must be something to do with the excel version i guess.

 

But thanks, i get the line of the solution!

 

amit_bhola_0-1587814761764.png