In Excel 2000 and Excel 2003, I am trying to validate if specific (x,y)
coordinates fall within the area bounded by 4 intersecting curves with
equations "y=f(x)", and am looking for help to set this up. I have tried the
normal logic of IF, AND, and OR, but have not been successful at all.

Of the four functions of the type "y=f(x)". the fourth one is the locus of
the human eye response to colors, and is a very complex function. The others
are more like "y = mx+c". Since the complex function is representing the
boundary of an instrument's data output, the data will always be bounded on
that side. I am currently more concerned with the first 3 equations of the
first order, and would just like to ensure that my data points are within
their bounds.

Has anyone ever tried this in Excel, or am I the first one to venture into
this area?

--
Mukesh

Mukesh,

You cannot do this with worksheet functions for any group of four functions, unless your know more
about how your y value should relate to the other functions. For example, if it should be greater
than the first, less than the second, less than the third, and greater than the fourth, for any
given x value, then you could reliably say when it will fall in that area. You may need to make a
truth table for various X ranges, and then compare the results of the various comparisons with the
values stored in the truth table.

HTH,
Bernie
MS Excel MVP


"Mukesh" wrote in message
...
In Excel 2000 and Excel 2003, I am trying to validate if specific (x,y)
coordinates fall within the area bounded by 4 intersecting curves with
equations "y=f(x)", and am looking for help to set this up. I have tried the
normal logic of IF, AND, and OR, but have not been successful at all.

Of the four functions of the type "y=f(x)". the fourth one is the locus of
the human eye response to colors, and is a very complex function. The others
are more like "y = mx+c". Since the complex function is representing the
boundary of an instrument's data output, the data will always be bounded on
that side. I am currently more concerned with the first 3 equations of the
first order, and would just like to ensure that my data points are within
their bounds.

Has anyone ever tried this in Excel, or am I the first one to venture into
this area?

--
Mukesh

Thank you very much for your response.

The relationship of y to x in the y=mx+c is as follows:
y=0.065+0.805x
y=0.400-x
x=0.133+0.600y

Also, while the points at which these lines intersect with the human eye
response curve is not known, the general bounds are. This gives the following
coordinates of the bounds:
x1,y1 = 0, 0.065
x2,y2 = 0.185596, 0.214404
x3,y3 = 0.233125, 0.166875
x4,y4 = 0.133, 0

How can these be used to set up a formula to see if my measurements fall
within these bounds?
--
Mukesh


"Bernie Deitrick" wrote:

Mukesh,

You cannot do this with worksheet functions for any group of four functions, unless your know more
about how your y value should relate to the other functions. For example, if it should be greater
than the first, less than the second, less than the third, and greater than the fourth, for any
given x value, then you could reliably say when it will fall in that area. You may need to make a
truth table for various X ranges, and then compare the results of the various comparisons with the
values stored in the truth table.

HTH,
Bernie
MS Excel MVP


"Mukesh" wrote in message
...
In Excel 2000 and Excel 2003, I am trying to validate if specific (x,y)
coordinates fall within the area bounded by 4 intersecting curves with
equations "y=f(x)", and am looking for help to set this up. I have tried the
normal logic of IF, AND, and OR, but have not been successful at all.

Of the four functions of the type "y=f(x)". the fourth one is the locus of
the human eye response to colors, and is a very complex function. The others
are more like "y = mx+c". Since the complex function is representing the
boundary of an instrument's data output, the data will always be bounded on
that side. I am currently more concerned with the first 3 equations of the
first order, and would just like to ensure that my data points are within
their bounds.

Has anyone ever tried this in Excel, or am I the first one to venture into
this area?

--
Mukesh

Mukesh,

If you graph those equations, there is no area that is fully bounded. So unless x = 0.133+0.600y
is really y = (x-0.133)/0.6 instead of y=0.133+0.600x, or you need to include more of the xy range
of the bounds of the response curve.

HTH,
Bernie
MS Excel MVP


"Mukesh" wrote in message
...


Thank you very much for your response.

The relationship of y to x in the y=mx+c is as follows:
y=0.065+0.805x
y=0.400-x
x=0.133+0.600y

Also, while the points at which these lines intersect with the human eye
response curve is not known, the general bounds are. This gives the following
coordinates of the bounds:
x1,y1 = 0, 0.065
x2,y2 = 0.185596, 0.214404
x3,y3 = 0.233125, 0.166875
x4,y4 = 0.133, 0

How can these be used to set up a formula to see if my measurements fall
within these bounds?
--
Mukesh


"Bernie Deitrick" wrote:

Mukesh,

You cannot do this with worksheet functions for any group of four functions, unless your know
more
about how your y value should relate to the other functions. For example, if it should be
greater
than the first, less than the second, less than the third, and greater than the fourth, for any
given x value, then you could reliably say when it will fall in that area. You may need to make a
truth table for various X ranges, and then compare the results of the various comparisons with
the
values stored in the truth table.

HTH,
Bernie
MS Excel MVP


"Mukesh" wrote in message
...
In Excel 2000 and Excel 2003, I am trying to validate if specific (x,y)
coordinates fall within the area bounded by 4 intersecting curves with
equations "y=f(x)", and am looking for help to set this up. I have tried the
normal logic of IF, AND, and OR, but have not been successful at all.

Of the four functions of the type "y=f(x)". the fourth one is the locus of
the human eye response to colors, and is a very complex function. The others
are more like "y = mx+c". Since the complex function is representing the
boundary of an instrument's data output, the data will always be bounded on
that side. I am currently more concerned with the first 3 equations of the
first order, and would just like to ensure that my data points are within
their bounds.

Has anyone ever tried this in Excel, or am I the first one to venture into
this area?

--
Mukesh

Bernie,

As i mentioned in the previous posting, the bounds decided by the human eye
response close the fourth side that is left open. Since the instrument that
provides the x and y coordinates of the point that I want to test is designed
to replicate the human eye response, I am confident that by telling if the
point lies within the bounds of the three linear equations that I have
provided, it will automatically be proven that the fourth bound has
implicitly been tested for.
--
Mukesh


"Bernie Deitrick" wrote:

Mukesh,

If you graph those equations, there is no area that is fully bounded. So unless x = 0.133+0.600y
is really y = (x-0.133)/0.6 instead of y=0.133+0.600x, or you need to include more of the xy range
of the bounds of the response curve.

HTH,
Bernie
MS Excel MVP


"Mukesh" wrote in message
...


Thank you very much for your response.

The relationship of y to x in the y=mx+c is as follows:
y=0.065+0.805x
y=0.400-x
x=0.133+0.600y

Also, while the points at which these lines intersect with the human eye
response curve is not known, the general bounds are. This gives the following
coordinates of the bounds:
x1,y1 = 0, 0.065
x2,y2 = 0.185596, 0.214404
x3,y3 = 0.233125, 0.166875
x4,y4 = 0.133, 0

How can these be used to set up a formula to see if my measurements fall
within these bounds?
--
Mukesh


"Bernie Deitrick" wrote:

Mukesh,

You cannot do this with worksheet functions for any group of four functions, unless your know
more
about how your y value should relate to the other functions. For example, if it should be
greater
than the first, less than the second, less than the third, and greater than the fourth, for any
given x value, then you could reliably say when it will fall in that area. You may need to make a
truth table for various X ranges, and then compare the results of the various comparisons with
the
values stored in the truth table.

HTH,
Bernie
MS Excel MVP


"Mukesh" wrote in message
...
In Excel 2000 and Excel 2003, I am trying to validate if specific (x,y)
coordinates fall within the area bounded by 4 intersecting curves with
equations "y=f(x)", and am looking for help to set this up. I have tried the
normal logic of IF, AND, and OR, but have not been successful at all.

Of the four functions of the type "y=f(x)". the fourth one is the locus of
the human eye response to colors, and is a very complex function. The others
are more like "y = mx+c". Since the complex function is representing the
boundary of an instrument's data output, the data will always be bounded on
that side. I am currently more concerned with the first 3 equations of the
first order, and would just like to ensure that my data points are within
their bounds.

Has anyone ever tried this in Excel, or am I the first one to venture into
this area?

--
Mukesh

Give this code a try. Add a Module to your project (click Insert/Module from
the VBA menu bar) and paste the following into its code window...

Public Type POINTAPI
X As Double
Y As Double
End Type

Public MyRegion() As POINTAPI

Public Function PtInPoly(Poly() As POINTAPI, _
ByVal Xray As Double, _
ByVal YofRay As Double) As Boolean
Dim X As Long
Dim Yintersect As Double
Dim PolyCount As Long
Dim NumSidesCrossed As Long
PolyCount = 1 + UBound(Poly) - LBound(Poly)
For X = LBound(Poly) To UBound(Poly)
If Poly(X).X Xray Xor Poly((X + 1) Mod PolyCount).X Xray Then
Yintersect = Y_at_X_Ray(Xray, Poly(X), Poly((X + 1) Mod PolyCount))
If Yintersect YofRay Then
NumSidesCrossed = NumSidesCrossed + 1
End If
End If
Next
If NumSidesCrossed Mod 2 Then PtInPoly = True
End Function

Private Function Y_at_X_Ray(ByVal Xray As Double, _
p1 As POINTAPI, _
p2 As POINTAPI) As Double
Dim m As Single
Dim b As Single
m = (p2.Y - p1.Y) / (p2.X - p1.X)
b = (p1.Y * p2.X - p1.X * p2.Y) / (p2.X - p1.X)
Y_at_X_Ray = m * Xray + b
End Function

Next, somewhere in your code, execute the following in order to initialize
everything...

ReDim MyRegion(0 To 2)
MyRegion(0).X = 0.185596
MyRegion(0).Y = 0.214404
MyRegion(1).X = 0.233125
MyRegion(1).Y = 0.166875
MyRegion(2).X = 0.332689
MyRegion(2).Y = 0.332814

The coordinates being assigned above are the intersection points of the
three lines you posted equations for. If the equations change, you will need
to calculate the new intersection coordinates and assign them in the above
code. By the way, when I calculated these intersection points, I assumed the
equation x=0.133+0.6y was written correctly (the x and y variables are
reversed from the other two and from what one would normally expect). Okay,
now you can test any point for being inside the boundary formed by the three
lines by executing code similar to this...

Dim Xcoord As Double
Dim Ycoord As Double
Xcoord = 0.25
Ycoord = 0.24
MsgBox PtInPoly(MyRegion, Xcoord, Ycoord)
Xcoord = 0.52
Ycoord = 0.29
MsgBox PtInPoly(MyRegion, Xcoord, Ycoord)

The PtInPoly (point in polygon) function returns True for the first point
and False for the second point. Note that I did not show an event procedure
block for either of the last two code snippets because they can be executed
wherever necessary within your code. Perhaps the first snippet (the
initializing one) could run from a UserForm Initialize event (assuming you
have a UserForm) or from a worksheet's Activate event; whereas the second
code snippet could be run from, say, a command button.

Anyway, give it a try and let me know if it works for you or not. If not,
let me know in what way and I will try to adjust the code for you.

Rick


"Mukesh" wrote in message
...
Bernie,

As i mentioned in the previous posting, the bounds decided by the human
eye
response close the fourth side that is left open. Since the instrument
that
provides the x and y coordinates of the point that I want to test is
designed
to replicate the human eye response, I am confident that by telling if the
point lies within the bounds of the three linear equations that I have
provided, it will automatically be proven that the fourth bound has
implicitly been tested for.
--
Mukesh


"Bernie Deitrick" wrote:

Mukesh,

If you graph those equations, there is no area that is fully bounded. So
unless x = 0.133+0.600y
is really y = (x-0.133)/0.6 instead of y=0.133+0.600x, or you need to
include more of the xy range
of the bounds of the response curve.

HTH,
Bernie
MS Excel MVP


"Mukesh" wrote in message
...


Thank you very much for your response.

The relationship of y to x in the y=mx+c is as follows:
y=0.065+0.805x
y=0.400-x
x=0.133+0.600y

Also, while the points at which these lines intersect with the human
eye
response curve is not known, the general bounds are. This gives the
following
coordinates of the bounds:
x1,y1 = 0, 0.065
x2,y2 = 0.185596, 0.214404
x3,y3 = 0.233125, 0.166875
x4,y4 = 0.133, 0

How can these be used to set up a formula to see if my measurements
fall
within these bounds?
--
Mukesh


"Bernie Deitrick" wrote:

Mukesh,

You cannot do this with worksheet functions for any group of four
functions, unless your know
more
about how your y value should relate to the other functions. For
example, if it should be
greater
than the first, less than the second, less than the third, and greater
than the fourth, for any
given x value, then you could reliably say when it will fall in that
area. You may need to make a
truth table for various X ranges, and then compare the results of the
various comparisons with
the
values stored in the truth table.

HTH,
Bernie
MS Excel MVP


"Mukesh" wrote in message
...
In Excel 2000 and Excel 2003, I am trying to validate if specific
(x,y)
coordinates fall within the area bounded by 4 intersecting curves
with
equations "y=f(x)", and am looking for help to set this up. I have
tried the
normal logic of IF, AND, and OR, but have not been successful at
all.

Of the four functions of the type "y=f(x)". the fourth one is the
locus of
the human eye response to colors, and is a very complex function.
The others
are more like "y = mx+c". Since the complex function is representing
the
boundary of an instrument's data output, the data will always be
bounded on
that side. I am currently more concerned with the first 3 equations
of the
first order, and would just like to ensure that my data points are
within
their bounds.

Has anyone ever tried this in Excel, or am I the first one to
venture into
this area?

--
Mukesh

Oh, I forgot to mention... there is a minor problem with the function I
posted... points that fall exactly on the boundary will report True
sometimes and False other times. If that might be a problem to you, I have
code somewhere that can report whether a point is within a specified
tolerance distance of a line... it is bundled up in the functionality of
another code solution I once posted (in the compiled VB newsgroups) but I
could probably modify it for use with the code I posted here. Let me know if
this would be something you would need.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this code a try. Add a Module to your project (click Insert/Module
from the VBA menu bar) and paste the following into its code window...

Public Type POINTAPI
X As Double
Y As Double
End Type

Public MyRegion() As POINTAPI

Public Function PtInPoly(Poly() As POINTAPI, _
ByVal Xray As Double, _
ByVal YofRay As Double) As Boolean
Dim X As Long
Dim Yintersect As Double
Dim PolyCount As Long
Dim NumSidesCrossed As Long
PolyCount = 1 + UBound(Poly) - LBound(Poly)
For X = LBound(Poly) To UBound(Poly)
If Poly(X).X Xray Xor Poly((X + 1) Mod PolyCount).X Xray Then
Yintersect = Y_at_X_Ray(Xray, Poly(X), Poly((X + 1) Mod PolyCount))
If Yintersect YofRay Then
NumSidesCrossed = NumSidesCrossed + 1
End If
End If
Next
If NumSidesCrossed Mod 2 Then PtInPoly = True
End Function

Private Function Y_at_X_Ray(ByVal Xray As Double, _
p1 As POINTAPI, _
p2 As POINTAPI) As Double
Dim m As Single
Dim b As Single
m = (p2.Y - p1.Y) / (p2.X - p1.X)
b = (p1.Y * p2.X - p1.X * p2.Y) / (p2.X - p1.X)
Y_at_X_Ray = m * Xray + b
End Function

Next, somewhere in your code, execute the following in order to initialize
everything...

ReDim MyRegion(0 To 2)
MyRegion(0).X = 0.185596
MyRegion(0).Y = 0.214404
MyRegion(1).X = 0.233125
MyRegion(1).Y = 0.166875
MyRegion(2).X = 0.332689
MyRegion(2).Y = 0.332814

The coordinates being assigned above are the intersection points of the
three lines you posted equations for. If the equations change, you will
need to calculate the new intersection coordinates and assign them in the
above code. By the way, when I calculated these intersection points, I
assumed the equation x=0.133+0.6y was written correctly (the x and y
variables are reversed from the other two and from what one would normally
expect). Okay, now you can test any point for being inside the boundary
formed by the three lines by executing code similar to this...

Dim Xcoord As Double
Dim Ycoord As Double
Xcoord = 0.25
Ycoord = 0.24
MsgBox PtInPoly(MyRegion, Xcoord, Ycoord)
Xcoord = 0.52
Ycoord = 0.29
MsgBox PtInPoly(MyRegion, Xcoord, Ycoord)

The PtInPoly (point in polygon) function returns True for the first point
and False for the second point. Note that I did not show an event
procedure block for either of the last two code snippets because they can
be executed wherever necessary within your code. Perhaps the first snippet
(the initializing one) could run from a UserForm Initialize event
(assuming you have a UserForm) or from a worksheet's Activate event;
whereas the second code snippet could be run from, say, a command button.

Anyway, give it a try and let me know if it works for you or not. If not,
let me know in what way and I will try to adjust the code for you.

Rick


"Mukesh" wrote in message
...
Bernie,

As i mentioned in the previous posting, the bounds decided by the human
eye
response close the fourth side that is left open. Since the instrument
that
provides the x and y coordinates of the point that I want to test is
designed
to replicate the human eye response, I am confident that by telling if
the
point lies within the bounds of the three linear equations that I have
provided, it will automatically be proven that the fourth bound has
implicitly been tested for.
--
Mukesh


"Bernie Deitrick" wrote:

Mukesh,

If you graph those equations, there is no area that is fully bounded.
So unless x = 0.133+0.600y
is really y = (x-0.133)/0.6 instead of y=0.133+0.600x, or you need to
include more of the xy range
of the bounds of the response curve.

HTH,
Bernie
MS Excel MVP


"Mukesh" wrote in message
...


Thank you very much for your response.

The relationship of y to x in the y=mx+c is as follows:
y=0.065+0.805x
y=0.400-x
x=0.133+0.600y

Also, while the points at which these lines intersect with the human
eye
response curve is not known, the general bounds are. This gives the
following
coordinates of the bounds:
x1,y1 = 0, 0.065
x2,y2 = 0.185596, 0.214404
x3,y3 = 0.233125, 0.166875
x4,y4 = 0.133, 0

How can these be used to set up a formula to see if my measurements
fall
within these bounds?
--
Mukesh


"Bernie Deitrick" wrote:

Mukesh,

You cannot do this with worksheet functions for any group of four
functions, unless your know
more
about how your y value should relate to the other functions. For
example, if it should be
greater
than the first, less than the second, less than the third, and
greater than the fourth, for any
given x value, then you could reliably say when it will fall in that
area. You may need to make a
truth table for various X ranges, and then compare the results of the
various comparisons with
the
values stored in the truth table.

HTH,
Bernie
MS Excel MVP


"Mukesh" wrote in message
...
In Excel 2000 and Excel 2003, I am trying to validate if specific
(x,y)
coordinates fall within the area bounded by 4 intersecting curves
with
equations "y=f(x)", and am looking for help to set this up. I have
tried the
normal logic of IF, AND, and OR, but have not been successful at
all.

Of the four functions of the type "y=f(x)". the fourth one is the
locus of
the human eye response to colors, and is a very complex function.
The others
are more like "y = mx+c". Since the complex function is
representing the
boundary of an instrument's data output, the data will always be
bounded on
that side. I am currently more concerned with the first 3 equations
of the
first order, and would just like to ensure that my data points are
within
their bounds.

Has anyone ever tried this in Excel, or am I the first one to
venture into
this area?

--
Mukesh