Forum Discussion

adrianb99's avatar
adrianb99
Copper Contributor
Jun 29, 2022
Solved

Visualize Degree Position on a Ring

Hi,   for my bachelor's thesis I need to visualize circumferential positions on a ring.   More precisely: I have a number of components that need to be placed on a large ring. I have got the cir...
developer
excel
  • mtarler's avatar
    mtarler
    Jun 29, 2022

    adrianb99  I don't know that Excel is the best option for this.  Is this a 1 time thing?  or do you need to be able to easily feed a set of coordinates and have the graphic update?  The closest to a built-in functionality in Excel (i.e. not write a macro/VBA to create this) might be the doughnut graph:

     

mtarler's avatar
mtarler
Silver Contributor
Jun 29, 2022

adrianb99  I don't know that Excel is the best option for this.  Is this a 1 time thing?  or do you need to be able to easily feed a set of coordinates and have the graphic update?  The closest to a built-in functionality in Excel (i.e. not write a macro/VBA to create this) might be the doughnut graph:

 

  • adrianb99's avatar
    adrianb99
    Copper Contributor
    Jun 29, 2022
    Hi mtarler, thanks for the quick reply!

    Yeah I was already afraid Excel might not be the best option for this, but unfrotunately it has to be in excel. But your solution might not be too bad since I do need to update the data frequently. Also I figured out how to get the chart the way it is in your screenshot.

    I might ask you later for further advice regarding that, if that's ok.
    Thanks for your help so far though, brought me a step further!
    • mtarler's avatar
      mtarler
      Silver Contributor
      Jun 29, 2022

      adrianb99  Ok so i thought I'd have some fun with this and came up with this:

      It lets you enter the nominal position (degrees) for each object, the size of the object (degrees) and then through this following formula calculates the 'gaps' and object sizes accordingly.

      =LET(names,C2:C7,pos,A2:A7,sizes,B2:B7,start,MIN(pos-sizes/2),end,MAX(pos+sizes/2),IF((start<0)+(end>360),"Obj Position + 1/2 Size must always be between 0 and 360",
  LET(cc,2*ROWS(pos)-1+(start>0)+(end<360),
           s, SEQUENCE(cc),
           ss,INT(SEQUENCE(cc,1,1+(start=0)/2,0.5)),
          namelist,CHOOSE(MOD(s+(start>0),2)+1," ",INDEX(names,ss)),
          posList,CHOOSE(IF(s=cc,3,MOD(s+(start>0),2)+1),INDEX(pos,ss)-INDEX(sizes,ss)/2,INDEX(pos,ss)+INDEX(sizes,ss)/2,360),
          valList,posList-IF(s>1,INDEX(posList,s-1),0),
          out, MAKEARRAY(cc,3,LAMBDA(r,c,CHOOSE(c,INDEX(namelist,r),INDEX(valList,r),INDEX(posList,r)))),
out
  ))
)
      • adrianb99's avatar
        adrianb99
        Copper Contributor
        Jul 06, 2022
        Hi mtarler,

        sorry for the late reply, was super busy!

        But your coding seems pretty amazing! Thanks so much for all the effort!
        There is just one issue, the number of elements that need to be placed on the ring is pretty big. I would need to place the labels outside the ring somehow, though I think I can do that manually.

        But I will definetely try out your proposal, thatk's so much again for the effort!

Resources