Hi I'm looking to be able to make a graph that shows times a league scores goals
I have 20+ games of data
And would like it to look like the photo
I've tried looking on YouTube but thought I'd ask the experts
My data is set in 5min intervals
I can pretty much change the table to what ever is needed.
Any help would be greatly appreciated
That reminds me of this wind rose chart we worked on a while ago: https://www.excelforum.com/excel-cha...wind-rose.html I expect a similar approach will work for this chart.
Originally Posted by shg
Perfect I'll have a look at this looks amazing thank you
ok so my sheet runs a dropdown from the cell (england) in red
it the collects the data into the tables you see
what i was trying to make one graph like the photo i posted to show when the leagues score goals
is it possible or do you need the tables in a different kind??
Yes it is possible, and yes, you will need the tables in a different arrangement. If you understood the wind rose plot that was created in the other thread, you should realize that each 5 minute (or 7.5 minute in your sample chart) entry needs multiple entries in the chart table (just like the wind rose chart needed multiple copies of the N NW etc entries to get the effect he wanted).
As a start to yours I:
1) I need multiple time "category" entries to build the radar chart. I decide to try 2 copies and see if that is enough. I enter 5 into H46 and I46. In J46, I enter =H46+5, and copy across until I have a couple of 85 entries.
2) I enter a lookup function in H47 to extract the data from the Finland table. =HLOOKUP(H46,$H$34:$Y$35,2). Copy across.
2a) I note that most of your "times" in row 34 are numbers stored as text, causing the HLOOKUP() to fail, so I convert all of those numbers stored as text to numbers.
3) In order to get alternating colors in the radar segments, I move every other pair of time entries down a row (to get 2 data series).
4) Select H46:AO48 -> Insert charts -> other -> radar area chart.
4a) Excel decides I intended three data series, so I bring up the Select Data dialog and tell Excel to use row 46 as the Category X values.
5) Look at the chart and see if it is beginning to show what I am trying to show.
Are we making progress?
I tried to get my head around the wind graph but got lost in all the look up formulas.
I think once I've done one I'll be able to do more.
I will try the above later today after work
Thank you mr shorty
got to here.. and graph looks like this...
Copy across.
2a) I note that most of your "times" in row 34 are numbers stored as text, causing the HLOOKUP() to fail, so I convert all of those numbers stored as text to numbers.
3) In order to get alternating colors in the radar segments, I move every other pair of time entries down a row (to get 2 data series).
4) Select H46:AO48 -> Insert charts -> other -> radar area chart.
4a) Excel decides I intended three data series, so I bring up the Select Data dialog and tell Excel to use row 46 as the Category X values.
5) Look at the chart and see if it is beginning to show what I am trying to show.
2a) Times in row 34 are still numbers stored as text, so I convert them to numbers (different strategies: https://support.office.com/en-us/art...1-c5bad0f0a885 I used the "paste special and multiply" strategy).
3) To move alternating segments into a second row, I selected J47:K47 -> cut (ctrl-x) -> select J48 -> paste (ctrl-v). Repeat for N47:O47, R47:S47, and so on.
4) It appears that Excel created a nice radar chart with row 46 as one data series and row 47 as a second data series. It was not smart enough to see that you wanted row 46 to be the horizontal category axis data.
4a) Bring up the Select data dialog
4b) Select the first series -> Edit series -> point it to row 48 instead of row 46.
4c) In the "horizontal category axis" field, select edit, and select H46:AO46
4d) Select chart -> change chart type -> filled radar chart.
Is that closer?
defo getting somewhere here!!!!!!
That is your basic filled radar chart. If Select Data -> Edit horizontal category axis labels -> Select H46:AO46, I have a chart that shows me how many goals were scored during each "block" of time. The easy part of this is mostly done (maybe a few cosmetic things could be changed). At this point, I would compare what you have with your picture in post #1. Which elements or aspects of the chart are essential, and which are you willing to let go? We have done the easy part. To get more like the picture, we are going to need to implement some tricks, maybe some combination charts and dummy series. I would identify the essential elements so I know what I can let slide.
OF I WOULD LOVE THE FOR THE OUTSIDE OF THE GRAPH TO MAYBE GO UP IN 10 MIN INSTEAD OF 15
i am not too bothered to comparing at the moment so if we can get one like it id be a happy man
The picture shows increments of 15 minutes. The data in your sample sheet (and chart) are based on 5 minute increments. To get 10 minute increments, you need to summarize the data by 10 minute increments and build the chart source data based on 10 minute categories. This might be as simple as:
1) Enter 10 into H46 and I46, then change the formula in J46 to =H46+10 and copy across.
2) If you change the data summary in rows 34:36, then the lookup formula in row 47 may not need to change. Or you could change the lookup formula into a new summary formula (a SUMIFS() or similar) that would add the 5 and 10, 15 and 20, 25 and 30, etc. data groups together. I don't have a specific recommendation for this, because I don't know how you would want to do this, where the data in rows 34:36 came from, and so on. One way or another, change the charts data so it is based on 10 minute increments instead of 5 minute increments, and your chart should follow suit.
3) Once you have adjusted the formulas in rows 46:48, go into the chart and adjust the ranges referenced by the chart to the smaller ranges.
You should be able to get whatever breakdown you want by summarizing the data according to the number/size of divisions you want and by applying the correct number of "categories" (columns) in the chart source data.
OK SO I ADDED A 15 MIN INTERVAL table.
still finding it a bit difficult as to how the numbers effect the racar graph
Adding the filled radar chart to sheet1, I:
1) Found the lookup table in H38:M39. Noticed that the minutes in row 38 are numbers stored as text again, so I converted them to numbers.
2) In H46 and I46, I entered 15. In J46, I changed the formula to =H46+15 and copied across as far as needed.
3) Changed the lookup function in H47 and J48 to refer to H38:M39. Copy these formulas across into the yellow cells.
4) Found the new chart in N13:U26. Checked the series definitions. Both data series refer to row 47 and the category axis data is a default sequence of numbers.
4a) Edit series 1 to refer to H47:S47
4b) Edit series 2 to refer to H48:S48
4c) Edit the horizontal category axis data to refer to H46:S46.
That should be close to the chart with 15 minute increments. Because we used fewer overall "categories" in the chart, there are larger gaps between "slices". We could have used more columns per category (used 4 or 6 copies of each time interval or whatever we want) which will give us smoother slices and smaller gaps between slices.
hi again
completely dont know how ive got to this now..
are you able to edit in excel parts for me please
again i thank you for your help. got further in a day that i have in the last month !
I cannot tell whether you are trying for a 15 minute summary/chart like in your original picture or a 10 minute summary. I made my edits based on wanting a 15 minute summary, but also made a couple of notes on where to change for a 10 minute summary.
Note that the only real edit to the chart was a minor correction. Most of the edits for breaking the chart up into different time increments are in the spreadsheet itself -- not the chart.
ok this is coming a long very well
thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks