radar chart from duration (HH:mm:ss)

[mlotfi]

Hi,
I have a column that has :

3:35:21
3:23:32
3:28:03
4:36:50
3:39:41
5:05:25
3:33:26
3:44:50
3:25:24
....
...


I want to create a radar chart that show how many times ranges 2-3 hours happened, the same for 3-4 hours, 4-5 hours,..etc

Please your help is appreciated.

[MrShorty]

I want to create a radar chart that show how many times ranges 2-3 hours happened, the same for 3-4 hours, 4-5 hours,..etc

Charts don't really analyze data for us. If you want a chart that will show a count of something, then the first step will be to program the spreadsheet to provide those counts. For numeric data like that (assuming the times are actual time serial numbers and not text strings that look like times), I would probably use the FREQUENCY() function https://support.office.com/en-us/art...7-fd9ea898fdb9
0) If your data are text, use TIMEVALUE() or other function to convert times to numbers.
1) Enter your "bins" data
2) Enter the FREQUENCY() function (note that FREQUENCY() is an array function so handle it accordingly)
3) Insert Radar chart
4) Format Radar chart as desired

Where do you get stuck?

[mlotfi]

Thanks MrShorty,

I am a java programmer, never used excel before, this morning my manager wanted me to create a radar chart from that data, that column is time.

he wanted me to create ranges from that column :

for example :
3:35:21 will be 3-4
4:36:50 will be 4-5
5:05:25 will be 5-6
the chart will display the ranges (3-4, 4-5,....) like how many times they occurred.
Thanks.

[MrShorty]

I am a java programmer, never used excel before,

I am not a Java programmer. If you are more comfortable with Java, would it be easier for you to analyze the data in Java, output the results to a text file, then import that text file into Excel to create the chart? That is the way my mentor (who was a FORTRAN programmer) would do these sorts of things.

The desired output you describe is exactly what the FREQUENCY() function should do. Did you look over the help file, study the example? Did it make sense? What did you not understand?

[mlotfi]

is there any formula that convert :
3:35:21 to 3-4
and
4:36:50 to 4-5
?

[MrShorty]

With the time in A1, something like =CONCATENATE(ROUNDDOWN(A1*24,0),"-",ROUNDUP(A1*24,0)) will convert times to those text strings. If I understand the analysis you are trying to do, I don't see the value in converting times to text strings. I would:

1) Enter a column of bin values:
2:00:00 (for the 0 to 2 hour bin)
3:00:00 (for the 2 to 3 hour bin)
4:00:00 (for the 3 to 4 hour bin)
etc.
2) Enter the FREQUENCY() function
2a) Select the cells adjacent to the bins columns + 1 cell (to capture the "more" entry from the FREQUENCY() function.
2b) Enter =FREQUENCY(A1:A100,C1:C7) where A1:A100 has the raw times and C1:C7 has the bins. Confirm with ctrl-shift-enter.
3) If you want the "0-2", "3-4", etc. text strings to use for the chart, then include an additional column for these values/formulas (something like the CONCATENATE function above).

[mlotfi]

If I have :
Duration(E) range(F) frequency(G)

3:35:21 3-4
3:23:32 3-4
3:28:03 3-4
4:36:50 4-5
3:39:41 3-4
5:05:25 5-6
3:33:26 3-4
3:44:50 3-4
3:25:24 3-4
3:22:07 3-4

why do I need to create another column called bins ?

how to get the FREQUENCY of ranges ?
I did this : =COUNTIF(F2:F110,F2) to get the frequency, but it's not correct.

Thanks.

[MrShorty]

why do I need to create another column called bins ?

The second argument of the FREQUENCY() function needs a "bins" array. Note that this needs to be an array (not a single value like you have) and it needs to be an array of numbers ("3-4" is a text string, not a number).

I did this : =COUNTIF(F2:F110,F2) to get the frequency, but it's not correct.

I am surprised this is not correct. It should have returned a count of how many "3-4" entries there are in column F. I note that you are using relative references, so the COUNTIF() function will not copy down correctly, but the first entry should have been correct. (https://support.office.com/en-us/art...es_in_formulas click on "using references in formulas" to get a discussion of relative and absolute references).

I expect that, after getting a good computation of the frequency in column G, you will need to extract a table of "unique" values from column F so that you can get a good Radar Chart. If you base your radar chart on columns F and G, it is going to have all of those duplicate 3-4 entries (and, I assume, duplicates for all of the other ranges) and those duplicates are going to create an incorrect (or, at least, very difficult to read) radar chart. Rather than putting the COUNTIF() in column G, I would create a new table, where you put the "unique" entries from column F, then put the COUNTIF() formula in the adjacent column

Please Login or Register  to view this content.

[mlotfi]

COUNTIF() was correct for the first cell, the problem was when copping down, but when I used your last formula it worked.

now I have :

3:35:21 3-4 65
3:23:32 3-4 65
3:28:03 3-4 65
4:36:50 4-5 9
3:39:41 3-4 65
5:05:25 5-6 1
3:33:26 3-4 65
3:44:50 3-4 65
3:25:24 3-4 65
3:22:07 3-4 65
3:35:32 3-4 65
4:06:38 4-5 9
3:40:09 3-4 65
3:31:02 3-4 65
3:27:47 3-4 65
..........


how can I create a radar chart from these last two columns ?
Thanks lot.

[MrShorty]

Select the data for the radar chart -> Insert Tab -> Charts Group -> Other Charts -> Desired radar chart subtype (assuming 2010 is similar to 2007).

As I noted above, all of the duplicate 3-4 entries (and duplicate 4-5, etc.) are going to make this variation of the chart difficult or meaningless to read, because it will treat each duplicate as a new text/category entry and include it in the plot. You will probably need to pull out "unique" values from the table. If you are familiar with Excel's pivot table tools, that might be the easiest way (Range (F) as your row labels, and "Average of Frequency (G)" or even "Count of Range (F)" as your "values"". Then build the radar chart from the pivot table. http://www.excel-easy.com/data-analy...ot-tables.html

Or, as suggested, put your "unique" entries in a column and place your COUNTIF() function in the adjacent column.

[mlotfi]

Thanks lot for your help, I just created another spreadsheet coping the ranges and frequency without duplicate, then created the radar chart, this was manually, I think I have to start spending some time to learn excel, thanks again.