Solving Co-ordinate Geometry problem with Excel

Question

I have three curves, A, B & C represented by the X-Y co-ordinates in the following chart. The origins are at (2.5,100) on the (X,Y) ordinates.

My requirement, for a pre-defined X,Y value, is as follows -

If the defined X,Y value is to the left of curve-A, return the output "A"

If the defined X,Y value is to the left of curve-B & right of curve-A return the output "B"

If the defined X,Y value is to the left of curve-C & right of curve-B return the output "C"

If the defined X,Y value is to the right of curve-C, return the output "FALSE"

Thanks in anticipation.

Answer 1

It is difficult to understand the purpose of your trying. From your basic expression I had picked up you looked for the location of a point (x,y) between the curves, now it seems you look only for the mutual position (on y-level) of your curves. Provided the curves do not cross, their ranking is trivial in that case, isn’t it? BTW, if you possibly return to the first variant, I would recommend using quadratic regression curves for your data, in the case of A with proper limitation. A difficulty with aiming the curves to the point (2.5, 100) beneath the set of points (your example y=133.7 is below the C series) can be thus solved.

PB

Answer 2

Considering that mathematics is at the root of virtually everything rational in this universe, it is no surprise that my problem is mathematical.

The reason you do not see "any reason why" it should be solved arithmetically is because you are not privy to the context, constraints and compulsions involved in this exercise. Suffice it to say, in this case, solution by school-level arithmetic as you suggest, is not an option.

The actual curve equation is not known because the curves are generated from data collected during actual experiments conducted. Therefore one will have to rely on linear interpolation between identified data.

What is necessary - and is, indeed, the specific help that I seek, in respect of the attached file is :

1. In Row $3, identify minimum data NOT LESS THAN 133.7 (pre-defined Y) & find corresponding value in Row $2
2. Identify data just lower than above-mentioned identified data & find corresponding value in Row $2
3. With linear interpolation calculate X-value corresponding to Y=133.7. This should be NOT LESS THAN 5.27, the pre-defined X value

Given the nature of the curves, this alone is sufficient to return "A".

Similar work is required between Rows $2 & $4 and again between $4 & $6. For Rows $2  & $4, X corresponding to Y=133.7, i.e. 5.27 should be NOT LESS THAN x-value on Row $2 and NOT GREATER THAN x-value on Row $4. This should return "B" and so on.

Thanks.

<iframe src="https://skydrive.live.com/embed?cid=A20A4D997FDBE04E&resid=A20A4D997FDBE04E%21109&authkey=AB6L4UYj_QWy_oI&em=2" width="402" height="346" frameborder="0" scrolling="no"></iframe>

Answer 3

It is rather mathematical than charting issue, so it is somewhat out of scope of this group. I don’t see any reason why you should solve it in other than arithmetic way.  You should have these three curves defined algebraically, most probably with explicit functions, for example

y = 100 + A1 (x – 2.5) + A2 (x – 2.5)^2

and so on, for parameters B1, B2, C2 ,C3. (They should not cross each other!) For each of your test pairs x,y, you have to evaluate xA, xB, and xC from these three equation (very easy if you have reverse explicit form x =  function(y), otherwise you could use quadratic formulas, you could even use Solver) and now if x<xA then "A" else if x<xB then "B" etc.

Maybe your curves are empirical (defined by a series of discrete points); then the solution will be either complicated by aid of interpolating formulas, or simple according to what you will be able to read out of the chart.

Regards

PB

Answer 4

Here is a list of Excel functions that are available:

http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP010079186.aspx#BMmath_and_trigonometry_functions

If that does not help you may have to write a VB script for the results you want.