Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. -- meddington ------------------------------------------------------------------------ meddington's Profile: http://www.excelforum.com/member.php...o&userid=27012 View this thread: http://www.excelforum.com/showthread...hreadid=429071 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
Sharing data across worksheets within a workbook based on identifi | Excel Discussion (Misc queries) | |||
Adding total dollars based on specific data from another column | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
populating sheets based on data from parent sheets | Excel Discussion (Misc queries) |