Adjusting one data set based another

  • Thread starter Thread starter meddington
  • Start date Start date
M

meddington

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
 
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.
 
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'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.>[/
Click to expand...

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(B15>D15,(((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(B15>D15,(((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.
Click to expand...

I did this, but I'm not sure how to interpret the results. :confused:
 
Back
Top