Plotting logarthmic chart - N log N in Excel

2
votes

I am currently analysing and profiling Quick Sort with numerous data size. I have already collected the required data from the profiler. And I have also plotted the graph for the average run time against the data size.

Now I would like to plot the N log N chart as well so that I can compare the theoritical data against the data I have. However I am not able to plot the N log N graph using excel.

There is an option under Chart Design --> Add Chart Element --> Trendline --> Logarithmic. It also gives us the option to customise it as shown below.

enter image description here

However I am not entirely sure about plotting N log N using this feature. I need this for analysing the Quick Sort and few other sort algorithms.

1
excelplotchartsexcel-chartstrendline
I did that, but I wouldn't be able to compare two graphs visually. What I am trying to say here is for Insertion sort its O(nĖ†2). So after plotting the graph with the recorded data, I then to trendline and selected Polynomial of order 2 and checked on "Display R-squared value on chart". The R value was close to 1, somewhere around 0.9987 which proved that my analysis of Insertion Sort is good. I thought of doing something similar here as well with Quick Sort. ā€“ RDM
Hmmm. I'm still looking to do this in 2021.... Did you get a fix? ā€“ Lain

1 Answers

-1
votes

You have time(y-axis) and n(x-axis) and as you already did, you can plot a graph and get the trendline easily by using some options in Chart dialogue.

More importantly, you can also get the trendline equation by linest() (for linear regression). You should search and learn how to use linest() for sure. In short, what you have to do now is just to add another column and fill it n * log and then use linest() with those columns.

enter image description here

The image is a portion of what I have done many years ago. B3:B11 is n and L3:L11 is time for randomized input data. As you can see, you can say that for randomized input, quicksort's performance is the most well fitted to n*log(n) because its R^2 (L21, which you should look carefully its forumula) is very close to 1.0 and the largest among n and n^2. Likewise, for already sorted(M3:M11) or reversly sorted(N3:N11) input, you can see and say that its performance is almost n^2. Hope this will help you.