Hello
please i want to know how i can create function with vba editor to find the median and mode for the following data.
5, 5, 10, 10, 10, 10, 15, 15, 20 ,500
Hello
please i want to know how i can create function with vba editor to find the median and mode for the following data.
5, 5, 10, 10, 10, 10, 15, 15, 20 ,500
Last edited by exc05; 07-04-2017 at 08:12 AM.
If this is an extension of the previous thread, https://www.excelforum.com/excel-pro...-function.html then part of the question is how to do it without resorting to the built in functions.
Seeing that the list is sorted (will it always be sorted?) and small, will a simple, brute force type algorithm work? This page ( http://www.codinghelmet.com/?path=exercises/array-mode ) suggests a simple algorithm for sorted data (see the section titled "Quicksort solution"). The basic idea looks something like:
1) Store the first data point with a count of 1
2) Loop through the elements of the array and test if the current element is the same as the previous element.
2a) If the current element is the same as the previous, increment count for current value by 1
2b) If the current element is different from the previous, then check if the count for the current value is greater than the currently stored max count. If so, then store current value and count as mode. If not, then resume loop.
3) At the end of the loop you should have the mode of the array.
4) Median is simply the middle value if n is odd. It is the average of the two middle values if n is even.
Do you have specific questions about coding that in VBA? Do you need the algorithm to be able to handle multi-modal data (where there can be more than one possible mode)? Will the data always be sorted, or do you need a sorting algorithm or an algorithm that does not require the data array to be sorted? As I indicated in the other thread, there are many considerations in developing median and mode algorithms. It will be easier to help if you can narrow down your question to specific parts of your chosen algorithm rather than this kind of broad question.
Originally Posted by shg
Why do you want to do that?
is it just for fun?
Or some school project?
Or is it because you want to slightly edit how median and mode work?
If you want something done right... find a forum and ask an online expert.
Time flies like an arrow. Fruit flies like a banana.
Project school , i try to doing it but i can't
This works for the median:
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Thank you very much bro , any idea about mode
This works for me:
Please Login or Register to view this content.
Last edited by xladept; 07-03-2017 at 09:03 PM.
Another one.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Thank you all for helping me solve this
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks