Adjusting one data set based another
I'd like to compare two sets and report a result that shows how one data set would plot if related to the other set (sorry, I know that's poorly worded). Here's an example: Sample #1 Sample #2 25% 0.26 25% 0.264 30% 0.31 30% 0.317 35% 0.36 35% 0.365 40% 0.41 40% 0.426 45% 0.45 45% 0.5 50% 0.51 50% 0.569 55% 0.56 55% 0.644 60% 0.62 60% 0.72 65% 0.69 65% 0.779 70% 0.75 70% 0.835 75% 0.84 75% 0.901 80% 0.93 80% 0.968 85% 1.03 85% 1.049 90% 1.12 90% 1.108 95% 1.24 95% 1.194 100% 1.4 100% 1.242 So, in the above data for example, at 50%, sample #1 reports 0.51 in sample #1 and 0.56 in sample #2. What I want to do is create a formula that compares sample 1 to sample two and gives me the dot percent value where sample one needs to be to equal sample two. For example for 50%, sample #1's value of 0.51 would equal a percent value around 46%, as this is the area that a value of 0.51 would occur. Any suggestions on how to achieve this? m -- meddington ------------------------------------------------------------------------ meddington's Profile: http://www.excelforum.com/member.php...o&userid=27012 View this thread: http://www.excelforum.com/showthread...hreadid=429071 |
I'd develop equations that predict each Sample and then go from there.
I've plotted the data and one appears to be more of an exponential and the other linear. "meddington" wrote in message ... I'd like to compare two sets and report a result that shows how one data set would plot if related to the other set (sorry, I know that's poorly worded). Here's an example: Sample #1 Sample #2 25% 0.26 25% 0.264 30% 0.31 30% 0.317 35% 0.36 35% 0.365 40% 0.41 40% 0.426 45% 0.45 45% 0.5 50% 0.51 50% 0.569 55% 0.56 55% 0.644 60% 0.62 60% 0.72 65% 0.69 65% 0.779 70% 0.75 70% 0.835 75% 0.84 75% 0.901 80% 0.93 80% 0.968 85% 1.03 85% 1.049 90% 1.12 90% 1.108 95% 1.24 95% 1.194 100% 1.4 100% 1.242 So, in the above data for example, at 50%, sample #1 reports 0.51 in sample #1 and 0.56 in sample #2. What I want to do is create a formula that compares sample 1 to sample two and gives me the dot percent value where sample one needs to be to equal sample two. For example for 50%, sample #1's value of 0.51 would equal a percent value around 46%, as this is the area that a value of 0.51 would occur. Any suggestions on how to achieve this? m -- meddington ------------------------------------------------------------------------ meddington's Profile: http://www.excelforum.com/member.php...o&userid=27012 View this thread: http://www.excelforum.com/showthread...hreadid=429071 |
Graph each series and add a trendline to each. For Sample 1, make it a
linear series and for sample 2, make it an exponential series. You can format each trendline to display the equation on the chart. "meddington" wrote in message ... I'd like to compare two sets and report a result that shows how one data set would plot if related to the other set (sorry, I know that's poorly worded). Here's an example: Sample #1 Sample #2 25% 0.26 25% 0.264 30% 0.31 30% 0.317 35% 0.36 35% 0.365 40% 0.41 40% 0.426 45% 0.45 45% 0.5 50% 0.51 50% 0.569 55% 0.56 55% 0.644 60% 0.62 60% 0.72 65% 0.69 65% 0.779 70% 0.75 70% 0.835 75% 0.84 75% 0.901 80% 0.93 80% 0.968 85% 1.03 85% 1.049 90% 1.12 90% 1.108 95% 1.24 95% 1.194 100% 1.4 100% 1.242 So, in the above data for example, at 50%, sample #1 reports 0.51 in sample #1 and 0.56 in sample #2. What I want to do is create a formula that compares sample 1 to sample two and gives me the dot percent value where sample one needs to be to equal sample two. For example for 50%, sample #1's value of 0.51 would equal a percent value around 46%, as this is the area that a value of 0.51 would occur. Any suggestions on how to achieve this? m -- meddington ------------------------------------------------------------------------ meddington's Profile: http://www.excelforum.com/member.php...o&userid=27012 View this thread: http://www.excelforum.com/showthread...hreadid=429071 |
I'd develop equations that predict each Sample and then go from there. I've plotted the data and one appears to be more of an exponential and the other linear.[/ I came up with an equation that will give me the results I need, but now I'm having trouble implmenting it into a form that excel likes. Its basically two formulae, one for the situation where the value of sample 2 is less than the value of sample 1, and one for when the value of sample 2 is greater than the value of sample 1. =IF(B15"<"D15,(((D15-B15)/((B16-B15)/(A16-A15)))+A15)),IF(B15D15,(((D15-B14)/((B15-B14)/(A15-A14)))+A14)) But apparently I don't have it formatted correctly as I'm getting an error. No if I use the formulas individually, they work as expected. =IF(B15"<"D15,(((D15-B15)/((B16-B15)/(A16-A15)))+A15)) or =IF(B15D15,(((D15-B14)/((B15-B14)/(A15-A14)))+A14)) But when I put them together, error. So, is there a problem with the way I established the second IF statement? Any thoughts? Graph each series and add a trendline to each. For Sample 1, make it a linear series and for sample 2, make it an exponential series. You can format each trendline to display the equation on the chart. I did this, but I'm not sure how to interpret the results. :confused: -- meddington ------------------------------------------------------------------------ meddington's Profile: http://www.excelforum.com/member.php...o&userid=27012 View this thread: http://www.excelforum.com/showthread...hreadid=429071 |
All times are GMT +1. The time now is 10:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com