pentagon graph

[Marius44]

Hello to all


How do I put a regular pentagon in an excel graph knowing the length of the side and the width of the angle (108 °) formed on two sides.

Thanks in advance. Hello,
Mario

PS. I excuse for my english

 

[oldbrewer]

does it have to be part of a graph - the shapes tool has a pentagon...

 

[Marius44]

Thank you for your prompt reply

But i have to built a Chart whit VBA not with the tool

Bye,
Marius

 

[Anthony47]

Thank you for your prompt reply

But i have to built a Chart whit VBA not with the tool

Bye,
Marius

Hello Marius, ice to meet you.

I assume you'll need to calculate coordinates for pentagon, but also hexagon, heptagon, octagon and so on.
For this reason let me suggest the following Function:

Function MultiGon(ByVal LLen As Single, ByVal NLati As Long) As Variant
'see https://www.mrexcel.com/forum/excel-questions/1022782-pentagon-graph.html
Dim rArr(), Alpha As Double, Lato As Double, Ragg As Double, I As Long
'
ReDim rArr(1 To NLati, 1 To 2)
Alpha = 2 * Application.WorksheetFunction.Pi / NLati
Ragg = LLen / 2 / Sin(Alpha / 2)
For I = 1 To NLati
    rArr(I, 1) = Ragg * Sin(Alpha * I)
    rArr(I, 2) = Ragg * Cos(Alpha * I)
Next I
MultiGon = rArr
End Function

Then in your worksheet you shall use the following formula:

=MultiGon(SideLength, NumberOfSides)

You need to insert it as an array formula in a grid o 2 columns * NumberOfSides

For example, for a Pentagon:

=MultiGon(SideLength, 5)

on an array of 5 rows and 2 columns

Bye

 

[oldbrewer]

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]46.91[/TD]
[TD="align: right"]85.49[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]63.09[/TD]
[TD="align: right"]85.49[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]79.61[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]these are the coordinates[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]for a pentagon of side = 10 units[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]plot a scatter chart[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marius44]

Thanks to both


@oldbrever
Your suggestion in a scatter-graph shows me a staircase!

@Anthony47
Your suggestion would be PERFECT if it did not have a small defect: it does not close the figure as it lacks the last segment.
I've "adapted" my needs to your macro as shown below

Sub pentagono()
LLen = Range("J1"): NLati = Range("J2")
Range("K1:L12").ClearContents
Call Pentagon(LLen, NLati)
End Sub


Function Pentagon(ByVal LLen As Single, ByVal NLati As Long) As Variant
'see https://www.mrexcel.com/forum/excel-questions/1022782-pentagon-graph.html
Dim rArr(), Alpha As Double, Lato As Double, Ragg As Double, I As Long
'
LLen = 10
ReDim rArr(1 To NLati, 1 To 2)
Alpha = 2 * Application.WorksheetFunction.Pi / NLati
Ragg = LLen / 2 / Sin(Alpha / 2)
For I = 1 To NLati
    rArr(I, 1) = Ragg * Sin(Alpha * I)
    rArr(I, 2) = Ragg * Cos(Alpha * I)
    Cells(I, 11) = rArr(I, 1)
    Cells(I, 12) = rArr(I, 2)
Next I
Cells(I, 11) = rArr(1, 1)
Cells(I, 12) = rArr(1, 2)
'MultiGon = rArr
End Function

Thank you very much.
Ciao,
Mario

 

[Anthony47]

Thanks to both
@Anthony47
Your suggestion would be PERFECT if it did not have a small defect: it does not close the figure as it lacks the last segment.
I've "adapted" my needs to your macro as shown below
[. . .]
Thank you very much.
Ciao,
Mario

That correction is syntactly wrong and cannot work (you cannot use a Function to modify cells, eccept the caller ones); nor a Function need to be "Called" from another routine; and finally that would kill one of the objective of my Function (generate any regular poligon).
If you need one additional coordinate (to close the graph) my function become

Function MultiGon(ByVal LLen As Single, ByVal NLati As Long) As Variant
'see https://www.mrexcel.com/forum/excel-questions/1022782-pentagon-graph.html
Dim rArr(), Alpha As Double, Lato As Double, Ragg As Double, I As Long
'
ReDim rArr(1 To NLati + 1, 1 To 2)
Alpha = 2 * Application.WorksheetFunction.Pi / NLati
Ragg = LLen / 2 / Sin(Alpha / 2)
For I = 1 To NLati + 1
    rArr(I, 1) = Ragg * Sin(Alpha * I)
    rArr(I, 2) = Ragg * Cos(Alpha * I)
Next I
MultiGon = rArr
End Function

If you wish to use it in another macro, just use, for example:

Range("K1:L12") = MultiGon(Range("J1"), Range("J2"))

You correctly realized that if the range (K1:L12, in this case) is longer than the returned coordinates, the extra positions will be filled by error #N/A, that makes them invisible for the graph.

Ciao

 

[Marius44]

Ok, Ok is syntactically incorrect (you know me well and know that I'm not very orthodox) but FUNCTIONS (I can assure you). I still pay due attention and correct it.


Rather, if I do not ask too much, I would like a straight to place the Chart in the fourth upper right at the Cartesian plane and not with the intersection of the axes at the center of the polygon.
What should I correct? Thanks again.
Hello,
Marius

 

[Marius44]

Hi Anthony


Unfortunately, your last suggestion does not give me the hoped-for result. The mArr array is empty.
I do not understand why.

Byebye,
Marius


PS - my error of script. Excuse me.

 

[Anthony47]

Hi Anthony


Unfortunately, your last suggestion does not give me the hoped-for result. The mArr array is empty.
I do not understand why.

Byebye,
Marius


PS - my error of script. Excuse me.

Mario, please check your email box