Find the median, mode

[exc05]

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

[AlphaFrog]

Excel already has Median and Mode functions builtin.

[MrShorty]

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.

[exc05]

i can calculate mode and median simply in excel VBBA.png , but what i want is to customize a function for median and mode with vba editor to calculate mode and made like excel Vba.png

[scottiex]

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?

[exc05]

Project school , i try to doing it but i can't

[xladept]

This works for the median:

Please Login or Register  to view this content.

[exc05]

Thank you very much bro , any idea about mode

[xladept]

This works for me:

Please Login or Register  to view this content.

[bakerman2]

Another one.

Please Login or Register  to view this content.

[exc05]

Thank you all for helping me solve this