Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
Hello,
I have had a good google around and I can't find the solution to this problem:
I have an X-Y scatter graph with a linear regression line (and equation) through the data - that bit is OK.
The problem lies with the underlying data. Some of the X or Y data have a value of 0 (rather then being a empty cell) and so when plotted this has an effect on the regression equation - "pulling the line towards the zero point on the X axis).
Is there a work around that would ignore the 0 values, change them to something else that excel won't graph or similar, that saves me having to go though and delete all the 0 cells manually.
Any thoughts would be greatly appreciated.
Thanks!
do you mean that the cells for those points "should" be blank but have somehow gained status as a zero value ?
If they're valid data points I'd have thought they should be contributing to the regression
Scaredypants, they do have a value of "zero" but I want them to behave as if they are "blank".
Go to raw data - find and replace 0 with blank, match entire cell contents.
njee20 - are you talking about the "find and replace" function in the toolbar? Is there a formula that would do that?
you can search and replace (on the Edit menu) although I'm not a fan of replacing data like that - make sure you tick the "entire cells" box. I would be inclined to use
=if(B1=0,#N/A,B1)
which would replace the 0's with n/a which is ignored in the regression
poly, that's done it! I was trying to be to clever - that's a nice simple solution. Thanks.