odd shaped chart area envelope, how do I flag a plot falls outside the area?

[jetclub1]

i have a graph, actually it is an aircraft weight and balance chart. It has an odd shaped envelope. How do I create a warning if a plot falls outside this area? If the area was square it would be easy with =IF(and(j23<23%,j24>4000),"warning","ok")) etc etc. but what if the area is an odd shape where on one side x increase and y decreases, on the other it is the other way.
Any ideas? so basically I want to create a warning flag when the plot falls outside the designated area.
Am I getting too complicated here?

Thank you

 

[Gerald Higgins]

Hi, welcome to the board.

Have you already defined the envelope in Excel ?
If yes, how exactly have you done that ?

That might help us to identify things that are outside the envelope.

 

[jetclub1]

Hi, I didn't do the actual envelope. I can do most usual stuff but this has me baffled.

when I click on the line I get this

=SERIES(Worksheet!$AB$81:$AC$81,Worksheet!$AC$83:$AC$95,Worksheet!$AB$83:$AB$95,1)

the data it refers to is here (the row AB81 to AC81 is the name 'W&B envelope')

AB AC
83 39800 38.0%
84 46500 36.0%
85 49000 36.0%
86 49000 39.8%
87 44000 45.0%
88 38400 45.0%
89 39800 38.0%

there is no data in rows 82 or 90 to 95, there are six points plotted to make the envelope.
hope this helps.

Mike

 

[sr12345]

A few ways you might want to proceed...

1. Devise equations for each boundary (edge) of the performance envelope. Then, you could use the AND function to make sure that the value is less than or greater than all of the equations.

2. You could take a "bin" approach, and then have a lookup table with the min and max allowable values for the particular bin level. Here you are stepping the slopes of the lines, and would technically get slightly incorrect results for data points falling right along the edge.

Let's say your envelope is center of gravity versus allowable weight. You could create the lookup table using bins of 0.5 inches, for example.

CG Max Weight
22.0 1500
22.5 1517
23 1523
23.5 1528
etc.

 

[jetclub1]

Yes it is a CG /weight graph. This sounds interesting. I need a bit more basic explanation though as i have not done anything like this before. The bin idea would do probably do ok. Would that work where the line could go outside the top or left or right on the graph?

I would put a picture if I knew how

thanks everyone

 

[shg]

The last point seems odd. Usually the gross weight must go down as the CG moves aft, right? Here it increases when the station goes from 88 to 89.

 

[jetclub1]

Sorry, actually it is %MAC against weight. My error for not explaining, thank you.
It is easy to load this aircraft wrong in both directions for the same weight.

 

[shg]

The first column is irrelevant?

 

[jetclub1]

yes, the table didn't print as i typed it.
the first column is the row number, the two data columns should be headed AB and AC

 

[shg]

You could use a UDF:

<TABLE style="WIDTH: 222pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=294 border=0 x:str><COLGROUP><COL style="WIDTH: 37pt" span=6 width=49><TBODY><TR style="HEIGHT: 20.4pt" height=27><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.4pt; BACKGROUND-COLOR: #f3f3f3" width=49 height=27>Gross Wgt</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=49>%MAC</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=49></TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=49>Gross Wgt</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=49>%MAC</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=49>Inside?</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>39800</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.38">38%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(MIN($A$2:$A$7), MAX($A$2:$A$7))">48369</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.43" x:fmla="=RANDBETWEEN(MIN($B$2:$B$7)*100, MAX($B$2:$B$7)*100)/100">43%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=middle x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>46500</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.36">36%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(MIN($A$2:$A$7), MAX($A$2:$A$7))">42843</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.38" x:fmla="=RANDBETWEEN(MIN($B$2:$B$7)*100, MAX($B$2:$B$7)*100)/100">38%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=middle x:bool="TRUE">TRUE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>49000</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.36">36%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(MIN($A$2:$A$7), MAX($A$2:$A$7))">43329</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.41" x:fmla="=RANDBETWEEN(MIN($B$2:$B$7)*100, MAX($B$2:$B$7)*100)/100">41%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=middle x:bool="TRUE">TRUE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>49000</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.39800000000000002">40%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(MIN($A$2:$A$7), MAX($A$2:$A$7))">46299</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.45" x:fmla="=RANDBETWEEN(MIN($B$2:$B$7)*100, MAX($B$2:$B$7)*100)/100">45%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=middle x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>44000</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.45">45%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(MIN($A$2:$A$7), MAX($A$2:$A$7))">45634</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.42" x:fmla="=RANDBETWEEN(MIN($B$2:$B$7)*100, MAX($B$2:$B$7)*100)/100">42%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=middle x:bool="TRUE">TRUE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>38400</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.45">45%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(MIN($A$2:$A$7), MAX($A$2:$A$7))">38813</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.36" x:fmla="=RANDBETWEEN(MIN($B$2:$B$7)*100, MAX($B$2:$B$7)*100)/100">36%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=middle x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>39800</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.38">38%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(MIN($A$2:$A$7), MAX($A$2:$A$7))">46219</TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="0.37" x:fmla="=RANDBETWEEN(MIN($B$2:$B$7)*100, MAX($B$2:$B$7)*100)/100">37%</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=middle x:bool="TRUE">TRUE</TD></TR></TBODY></TABLE>

The formula in F2 and copied down is

=PtInConvexPoly(D2,E2, $A$2:$B$7)

Function PtInConvexPoly(X As Double, _
                        Y As Double, _
                        avdInp As Variant) As Variant
    ' shg 2010
 
    ' Returns True if the point {x,y} is inside the convex polygon
    ' represented by the 1-based, 2D array avdInp of {x,y} pairs.
 
    ' avdInp must contain at least three points, in rows or columns.
 
    ' All points in avdInp must be in CW or CCW order.
    Dim avd         As Variant
    Dim i           As Long
    Dim j           As Long
    Dim iSgn        As Long
    Dim iSgnMin     As Long
    Dim iSgnMax     As Long
 
    avd = avdInp
 
    PtInConvexPoly = CVErr(xlErrValue)
    With WorksheetFunction
        If UBound(avd, 1) < UBound(avd, 2) Then avd = .Transpose(avd)
        If .Count(avd) < UBound(avd, 1) * UBound(avd, 2) Or _
           UBound(avd, 1) < 3 Or _
           UBound(avd, 2) <> 2 Then Exit Function
    End With
 
    For i = 1 To UBound(avd, 1)
        j = i Mod UBound(avd, 1) + 1
        iSgn = Sgn((avd(j, 1) - X) * (avd(i, 2) - Y) - _
                   (avd(i, 1) - X) * (avd(j, 2) - Y))
        If iSgn < iSgnMin Then iSgnMin = iSgn
        If iSgn > iSgnMax Then iSgnMax = iSgn
    Next i
 
    PtInConvexPoly = iSgnMax - iSgnMin <= 1
End Function