I have 2 columns with data in them, basically representing a gaussian
distribution. Column A has the "X-axis" values and so is uniformly
ascending with no duplicates. Column B has the "Y-axis" values and
increases up to a maximum and then decreases again (this is data from
an instrument and so its not completely smooth but is close). An
example is below.

0 4
1 8
2 16
3 27
4 50
5 27
6 16
7 8
8 4

What I would like to do is get the 2 Column A values where the
corresponding column B value is half of the max (in the case above, 25
is not available so the closest is 27). I am trying to calculate the
difference between these values, so in the example, I would have 5-3.
Is there a way to do this?

Thanks.


--
BKGT
------------------------------------------------------------------------
BKGT's Profile: http://www.excelforum.com/member.php...o&userid=33862
View this thread: http://www.excelforum.com/showthread...hreadid=536387

In your example, 27 is the closest value, and two such values exist.
Will the two closest values always be the same or can they differ? For
example, can your data contain the following?

0 4
1 8
2 16
3 27 <-----
4 50
5 23 <-----
6 16
7 8
8 4

In article ,
BKGT wrote:

I have 2 columns with data in them, basically representing a gaussian
distribution. Column A has the "X-axis" values and so is uniformly
ascending with no duplicates. Column B has the "Y-axis" values and
increases up to a maximum and then decreases again (this is data from
an instrument and so its not completely smooth but is close). An
example is below.

0 4
1 8
2 16
3 27
4 50
5 27
6 16
7 8
8 4

What I would like to do is get the 2 Column A values where the
corresponding column B value is half of the max (in the case above, 25
is not available so the closest is 27). I am trying to calculate the
difference between these values, so in the example, I would have 5-3.
Is there a way to do this?

Thanks.

Thanks for the reply. Yes, the two values could be different as in your
example.


--
BKGT
------------------------------------------------------------------------
BKGT's Profile: http://www.excelforum.com/member.php...o&userid=33862
View this thread: http://www.excelforum.com/showthread...hreadid=536387

After taking another look at this, what result would you expect under
the next two scenarios?

First scenario...

0 4
1 8
2 16
3 48
4 50
5 18
6 16
7 8
8 4

Second scenario...

0 4
1 8
2 18
3 20
4 50
5 48
6 18
7 8
8 4

In article ,
BKGT wrote:

Thanks for the reply. Yes, the two values could be different as in your
example.

Domenic wrote...
After taking another look at this, what result would you expect under
the next two scenarios?

First scenario...

0 4
1 8
2 16
3 48
4 50
5 18
6 16
7 8
8 4
....

I think you'd have to assume the distribution should be symmetric and
that the max value is the median as well as the mode. If so, and if the
first column were named X and the second Y, you could use the array
formula

=INDEX(X,MATCH(MIN(ABS(Y-MAX(Y)/2)),ABS(Y-MAX(Y)/2),0))-INDEX(X,
MOD(COUNT(Y)-MATCH(MIN(ABS(Y-MAX(Y)/2)),ABS(Y-MAX(Y)/2),0),COUNT(Y))+1)

The distribution should be symmetric so that is a good assumption. I
will try your suggestion. Thanks.


--
BKGT
------------------------------------------------------------------------
BKGT's Profile: http://www.excelforum.com/member.php...o&userid=33862
View this thread: http://www.excelforum.com/showthread...hreadid=536387

In article . com,
"Harlan Grove" wrote:

I think you'd have to assume the distribution should be symmetric...

Is that because we're dealing with a 'gaussian distribution'? I
suspected as much, but couldn't be sure since I'm not familiar with it.

If so, and if the first column were named X and the second Y, you could use the array
formula

=INDEX(X,MATCH(MIN(ABS(Y-MAX(Y)/2)),ABS(Y-MAX(Y)/2),0))-INDEX(X,
MOD(COUNT(Y)-MATCH(MIN(ABS(Y-MAX(Y)/2)),ABS(Y-MAX(Y)/2),0),COUNT(Y))+1)

Is there any reason why MOD is included in the formula? I've analyzed
it, but can't seem to find one...

Hi Domenic,

Ideally, I would like to get 5 - 2 from the first scenario and 6 - 3
from the second. I believe the point you were getting at is the
rounding up or down problem if the next value is much larger or much
lower than half of the max value. The real data I have to work with
probably won't be largely effected by this because there are alot of
data points so the error should be minimal. I don't suppose there is a
way to interpolate between two points if an exact match is not found.


--
BKGT
------------------------------------------------------------------------
BKGT's Profile: http://www.excelforum.com/member.php...o&userid=33862
View this thread: http://www.excelforum.com/showthread...hreadid=536387

Hi BKGT,

One way to get an exact match is to use an x,y scatter
graph with 2 data series in it.
Assuming your data is in columns A + B and the following
0 4
1 8
2 16
3 27
4 50
5 23
6 16
7 8
8 4

Data series 1 would be
0 4
1 8
2 16
3 27
4 50 i.e. (A1:B5)

And data series 2 would be
4 50
5 23
6 16
7 8
8 4 i.e. (A5:B9)

You can then add a polynomial (4th order) trendline to
each series and check 'show equation on chart'.

The equation for the first series is

y = 0.4167x4 - 2.6667x3 + 7.0833x2 - 0.8333x + 4

And the second series is

y = 1.0833x4 - 27.333x3 + 256.42x2 - 1067.2x + 1688

Now ALL?? you have to do is plug your y value into
each equation (MAX/2 or 25 in this example) and you
can calculate the value of x. Unfortunately my
mathematical skills aren't up to the task at this bit. I can
extract the equations from linear regression trendlines and
use them in formulae but polynomials just leave me
blubbering. Any one else care to step up to the plate and
show how to extract these equations into usable formula
that will reduce the process to
1.Data Input 2.Read off results

Regards
Martin