Creating a triangle - Part 1

J

jgbexcel

New Member
Joined
May 8, 2023
Messages
11
Office Version
  1. 2013
Platform
  1. Windows
I have 2 connected triangles so I will post each separately to avoid confusion (mainly on my part!)
They are part of a design for an articulated arm. Excel will define the dimensions that I need to create my CAD drawing with.
My trig skills are non-existant, but I am fairly well versed in Excel and Lotus 123 before it (and its predecessor on my Radio-Shack TSR-80. Yeah, I'm that old.)

So, 1st triangle.
I have 3 sides known.
3 vertices are F C & B.
F & B are connected elsewhere to known XY co-ordinates.
Side FC and side BC are defined fixed lengths.
Side FB is calculated elsewhere, as point F will move.
I need to calculate XY of C
Included angles are irrelevant.
(Note: C & B are also part of the next triangle problem which I will post tomorrow)

jgb
 
Sort by date Sort by votes
Perpa said:
View attachment 91926View attachment 91927
jgb,
Here is one solution for Point D coordinates with the assumed known values.
I hope this is useful.
Perpa
Click to expand...
Sorry to get back to you so late. Got sidetracked.
It does look good, but I'm a bit confused with a few cells.
Was trying to integrate it with my sheet, using your numbers first, but got stuck at cell ref C13 and C14.
How are they calculated? Only interested in Solution 2 (C2 on the left)
Or, better yet, is there a way to download your mini-sheet into my sheet? If need be you can email it to me at jgberson@rogers.com

jgb
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Here is the sheet in xl2BB format which shows the formulae:

Excel Triangle Calculator 5_10_2923.xlsx
ABCDEFGH
1Knowns:Solution for the Coordinates at Point D
250xB
350yB
440xFLength FB=31.62278=SQRT((A2-A4)^2+(A3-A5)^2)
520yF
640FC
725BC
8
9
10Solution 1 for x373.58912xC1
11and Y341.72029yC1
12
13Solution 2 for x326.16088xC2
14and y357.52971yC2
15
16
17xB-xC2 =23.83912
18yC2-yB =7.529706
19
20cos(GBC) =0.953565=C17/A7
21arcCos(GBC)=0.3059385117.52899=DEGREES(D21)
22in radiansSince yC > yB
23Let Angle CBD = 60 degrees60Add 60 to E21
24
25Proceedure 1:
26If CBD +GBC < 9077.52899degrees
27xD =37.04326=A2-cos(E24*PI()/180)*60Length of BD = 60
28yD =108.5843=A3+SIN(E24*PI()/180)*60
29
30If CBD + GBC > 90 then Angle = GBD -90
31
32xD ==A2+Sin((GBD-90)*PI()/180)*60
33yD ==A3+Cos((GBD-90)*PI()/180)*60
Sheet6
Cell Formulas
RangeFormula
E4E4=SQRT((A2-A4)^2+(A3-A5)^2)
C10C10=A4+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A2-A4))/(2*((A2-A4)^2+(A3-A5)^2))+(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A3-A5)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C11C11=A5+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A3-A5))/(2*((A2-A4)^2+(A3-A5)^2))-(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A2-A4)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C13C13=A4+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A2-A4))/(2*((A2-A4)^2+(A3-A5)^2))-(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A3-A5)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C14C14=A5+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A3-A5))/(2*((A2-A4)^2+(A3-A5)^2))+(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A2-A4)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C17C17=A2-C13
C18C18=C14-A3
C20C20=C17/A7
D21D21=ACOS(C20)
E21E21=DEGREES(D21)
E26E26=E21+E23
C27C27=A2-COS(E26*PI()/180)*60
C28C28=A3+SIN(E26*PI()/180)*60
 
Upvote 0
Perpa said:
Here is the sheet in xl2BB format which shows the formulae:

Excel Triangle Calculator 5_10_2923.xlsx
ABCDEFGH
1Knowns:Solution for the Coordinates at Point D
250xB
350yB
440xFLength FB=31.62278=SQRT((A2-A4)^2+(A3-A5)^2)
520yF
640FC
725BC
8
9
10Solution 1 for x373.58912xC1
11and Y341.72029yC1
12
13Solution 2 for x326.16088xC2
14and y357.52971yC2
15
16
17xB-xC2 =23.83912
18yC2-yB =7.529706
19
20cos(GBC) =0.953565=C17/A7
21arcCos(GBC)=0.3059385117.52899=DEGREES(D21)
22in radiansSince yC > yB
23Let Angle CBD = 60 degrees60Add 60 to E21
24
25Proceedure 1:
26If CBD +GBC < 9077.52899degrees
27xD =37.04326=A2-cos(E24*PI()/180)*60Length of BD = 60
28yD =108.5843=A3+SIN(E24*PI()/180)*60
29
30If CBD + GBC > 90 then Angle = GBD -90
31
32xD ==A2+Sin((GBD-90)*PI()/180)*60
33yD ==A3+Cos((GBD-90)*PI()/180)*60
Sheet6
Cell Formulas
RangeFormula
E4E4=SQRT((A2-A4)^2+(A3-A5)^2)
C10C10=A4+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A2-A4))/(2*((A2-A4)^2+(A3-A5)^2))+(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A3-A5)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C11C11=A5+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A3-A5))/(2*((A2-A4)^2+(A3-A5)^2))-(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A2-A4)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C13C13=A4+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A2-A4))/(2*((A2-A4)^2+(A3-A5)^2))-(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A3-A5)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C14C14=A5+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A3-A5))/(2*((A2-A4)^2+(A3-A5)^2))+(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A2-A4)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C17C17=A2-C13
C18C18=C14-A3
C20C20=C17/A7
D21D21=ACOS(C20)
E21E21=DEGREES(D21)
E26E26=E21+E23
C27C27=A2-COS(E26*PI()/180)*60
C28C28=A3+SIN(E26*PI()/180)*60
Click to expand...
Thank you.
I need to simplify C10...C14 as I will need to repeat it about 20+ times when I graph this at various F positions.
Back to you later. But busy week ahead on other stuff.

jgb
 
Upvote 0
jgb,
Here is a worksheet that is a better layout and has some macros to make the job easier.
There is also some assistance in determining the quadrant into which line BC is heading.
This is important when determining the heading for line BD which is an offset angle to line BC.
So here is the worksheet with the formulas shown below....

Excel Triangle Calculator 5_10_2923.xlsm
ABCDEFGHIJKLM
1Knowns:Solution for the Coordinates at Point D
250xB
350yB
440xFLength FB=31.62278
520yF
640FC
725BC
860Angle CBD60BD
9Calculated:
10FB31.62278
11Solution 1 for x373.58912xC1
12and Y341.72029yC1
13
14Solution 2 for x326.16088xC2
15and y357.52971yC2
1641
17Solution Selected26.16088xC
1857.52971yC
1932
20xC-xB-23.8391This shows from B to C is going into quadrant 4
21yC-yB7.529706
22QUADRANTS
23After you identify which location of point C is the solution, you must determine which quadrant the line BC is pointing into.
24If yB > yC and xB>xC then BC heading is to quadrant 3.If yB < yC and xB< xC then BC heading is to quadrant 1.
25  
26If yB< yC and xB>xC then BC heading is to quadrant 4.If yB > yC and xB < xC then BC heading is to quadrant 2.
274 
28
29YBC in
30 Y axis is parallel to Y Axis at pt BRadiansDegreesCBD-YBC= angle from parallel to Y axis of BD
31 cos(YBC) =0.301188acos(YBC) =1.26485872.47101-12.471
32
33xD=37.04326Quad 4
34yD=108.5843
35
36xD= Quad 3
37yD= 
38Run macro 'Mk_List2' to add to RESULTS
39
40RESULTS
41FBxByBxFyFFCBCBDAngle CBDAngle YBDxDyD
4231.6227775050402040256060-12.47137.04326108.5843
43
44
45
46
47
48
49
50
Sheet7
Cell Formulas
RangeFormula
E4E4=SQRT((A2-A4)^2+(A3-A5)^2)
C10C10=SQRT((A2-A4)^2+(A3-A5)^2)
C11C11=A4+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A2-A4))/(2*((A2-A4)^2+(A3-A5)^2))+(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A3-A5)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C12C12=A5+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A3-A5))/(2*((A2-A4)^2+(A3-A5)^2))-(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A2-A4)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C14C14=A4+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A2-A4))/(2*((A2-A4)^2+(A3-A5)^2))-(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A3-A5)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C15C15=A5+((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)*(A3-A5))/(2*((A2-A4)^2+(A3-A5)^2))+(SQRT(A6^2-((A6^2-A7^2+(A2-A4)^2+(A3-A5)^2)/(2*SQRT((A2-A4)^2+(A3-A5)^2)))^2)*(A2-A4)/(SQRT((A2-A4)^2+(A3-A5)^2)))
C20:C21C20=C17-A2
C25C25=IF(AND(A3>C18,A2>C17),3,"")
I25I25=IF(AND(A3<C18,A2<C17),1,"")
C27C27=IF(AND(A3<C18,A2>C17),4,"")
I27I27=IF(AND(A3>C18,A2<C17),2,"")
B31B31=C21/A7
E31E31=ACOS(B31)
F31F31=DEGREES(E31)
G31G31=A8-F31
F33F33=IF(G31<0,A2+SIN(G31*PI()/180)*60,"")
F34F34=IF(G31<0,A3+COS(G31*PI()/180)*60,"")
F36F36=IF(G31>0,A2+SIN(G31*PI()/180)*60,IF(G31=0,A2,""))
F37F37=IF(G31>0,A3+COS(G31*PI()/180)*60,IF(G31=0,A3+D8,""))


I created two macros.
The first macro can help select the solution, 1 or 2. This is a
Worksheet macro, not a General macro. When you select either solution (the x or the y) in cells
C11 and C12 or C14 and C15, the macro copies both the x and y values into cells C17 and C18.
The spreadsheet then automatically recalculates the coordinates for point D.

VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("C11:C12,C14:C15"), Target) Is Nothing Then
        Select Case Target.Address(0, 0)
            Case "C11"
                Range("C11:C12").Copy
                Range("C17:C18").PasteSpecial xlValues
            Case "C12"
                Range("C11:C12").Copy
                Range("C17:C18").PasteSpecial xlValues
            Case "C14"
                Range("C14:C15").Copy
                Range("C17:C18").PasteSpecial xlValues
            Case "C15"
                Range("C14:C15").Copy
                Range("C17:C18").PasteSpecial xlValues
        End Select
    End If
End Sub


The second macro is a General type and can be run using Alt+F8, then select 'Mk_List2'
and 'run'; OR you can create a button to run this macro. It copies all the data used to
calculate point D coordinates into the last row of the worksheet. You would use this macro
after each calculation of point D. I hope this will be helpful
Perpa

VBA Code: 
Sub Mk_List2()
Dim LastRow, col, col2, rw As Long

LastRow = [A:L].Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
Cells(LastRow, 1).Value = Cells(4, "E").Value '= FB
col = 2
    For rw = 2 To 7
       Cells(LastRow, col).Value = Cells(rw, "A").Value
       col = col + 1
    Next rw

col2 = 8
Cells(LastRow, col2).Value = Cells(8, "D").Value  '= Length BD
Cells(LastRow, col2 + 1).Value = Cells(8, "A").Value   '= Angle at B =60 degrees
Cells(LastRow, col2 + 2).Value = Cells(31, "G").Value

    If Cells(31, "G").Value < 0 Then   'Quad 4
       Cells(LastRow, col2 + 3).Value = Cells(33, "F").Value '= xD
       Cells(LastRow, col2 + 4).Value = Cells(34, "F").Value '= yD
       Exit Sub
    End If
    
    If Cells(31, "G").Value >= 0 Then   'Quad 3
       Cells(LastRow, col2 + 3).Value = Cells(36, "F").Value   '= xD
       Cells(LastRow, col2 + 4).Value = Cells(37, "F").Value   '= yD
    End If
End Sub
 
Upvote 0
jbg,

A couple of items that need attention:

From post#11 you said:
"...is there a way to download your mini-sheet into my sheet?"

Yes. First you go to the mini-sheet you want to download (in post#14) and above the '1' (row label) are two sheets shown one above the other.
Hover over them with your mouse and it will display "Click to copy to clipboard", so click with your mouse.
Then Paste into a new worksheet cell A1 in your open Excel file. At first the copied cells will be darkened, so before you click elsewhere and unselect the entire sheet,
go to the Home tab and click on 'Wrap Text' to unwrap the text in this sheet. For some reason this app copies everything but wraps the text which makes it harder to read. So after the text is unwrapped, click a cell somewhere on the sheet and then save your file.

Another item:
In the mini-sheet in post#14 I noticed that I had mislabeled cell H36, it should read 'Quad 1' not 'Quad 3'.
You can change that after you download the sheet.
I hope this is helpful.
Perpa
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
String running first time but when looping back it isnt placing correctly.
Replies
1
Views
506
DanSMT
D
E
  • Question Question
How to make excel vba crash proof?
Replies
0
Views
2K
elimgo
E
E
  • Question Question
2 Issues: Inefficient Array Read/Write Procedure & Compile Error Procedure Too Large
Replies
3
Views
664
rlv01
R
DocAElstein
  • Question Question
VBA to expand an Outline ( Grouping ) . ( Open Grouped Rows )
Replies
1
Views
14K
DocAElstein
DocAElstein
D
  • Question Question
Transfering data over multiple Workbooks; Excel 2010
Replies
0
Views
946
Da3d4lu5
D

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top