kingofaces
Board Regular
- Joined
- Aug 23, 2010
- Messages
- 68
So LINEST can provide you with the line of best fit with slope and y-int as well as r^2, but can you instead find the r^2 while comparing your data to a different line, in my case y=1x + 0 (y-int=0 m=1)? I have an Observed value column with given data, and a Predicted column which originated from one of many models I am using to predict the Observed column.
I have multiple models each giving an output of 10 or so rows in the Predicted column. The ideal model is where Observed=Predicted, so I was hoping that an r^2 while fitting y=x instead of the given line of best fit would show which model's output most closely matches the Predicted column. Example of one part of the Observed and one model's (Predicted) data below:
I've tried calculating r^2 manually by using SSM/SST, but whenever I try to use x for the model y data (ideal y) my r^2 is over 1. If I use the actual line of best fit and apply it to the ideal y column rather than having it equal x then r^2 matches LINEST perfectly. Is there something I'm doing wrong either in my logic of this even working or simply just an arithmetic error somewhere? I'm wondering if there is an issue with trying to use r^2 to compare to a different line like this? Any thoughts on what might be happening with my r^2 would be appreciated. It could just be that what I'm trying to do simply doesn't work and I just keep missing whatever it is that's throwing a wrench into this. For one, SST= SSM+SSE (F16). However, E15 should also be the same taking the sum of (C2-C15)^2+(C3-C15)^2...etc. It's the same as long as I'm not using ideal y = Observed, but once I try y=Observed the two cells are no longer equal. There's an issue with SST in this case, but I'm stumped on the reason. Thanks.
I have multiple models each giving an output of 10 or so rows in the Predicted column. The ideal model is where Observed=Predicted, so I was hoping that an r^2 while fitting y=x instead of the given line of best fit would show which model's output most closely matches the Predicted column. Example of one part of the Observed and one model's (Predicted) data below:
I've tried calculating r^2 manually by using SSM/SST, but whenever I try to use x for the model y data (ideal y) my r^2 is over 1. If I use the actual line of best fit and apply it to the ideal y column rather than having it equal x then r^2 matches LINEST perfectly. Is there something I'm doing wrong either in my logic of this even working or simply just an arithmetic error somewhere? I'm wondering if there is an issue with trying to use r^2 to compare to a different line like this? Any thoughts on what might be happening with my r^2 would be appreciated. It could just be that what I'm trying to do simply doesn't work and I just keep missing whatever it is that's throwing a wrench into this. For one, SST= SSM+SSE (F16). However, E15 should also be the same taking the sum of (C2-C15)^2+(C3-C15)^2...etc. It's the same as long as I'm not using ideal y = Observed, but once I try y=Observed the two cells are no longer equal. There's an issue with SST in this case, but I'm stumped on the reason. Thanks.
