Forum Discussion
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...
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:
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!
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!
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
))
)- 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!- You're welcome. It was a fun kind of challenge and I kinda give myself a pat on the back for what I did. lol. Using my template you may need to minimize the size/width of each object. I also did a special condition for the start/stop of the objects at 0/360 deg (you can't have an object cross over that location). That said I believe I did NOT take into account the error case of object size and location causing overlap and did not include a special case for object size and location causing adjacent objects (i.e. no negative/buffer space in between). If you have lots of objects, maybe the formula could/should be re-done to eliminate that offset/buffer spacing and object size and just make the graph show object to object. So in my case you could have object at 45deg and 75deg and each be 10deg so it includes a buffer from 50-70deg of 'blank'. But if you have lots of objects then it could have obj1 end and obj2 start at the 1/2 way point in between the 2.