Mean of N largest values in a range of cells

[Cloclo1990]

1. Dear All, I need your help in creating a VBA code.
   
   I must create a user-defined function to calculate the mean of the N largest values in a range of cells.
   
   
   It is compulsory I use this function: Function MeanOfLargest (DataRange as Range, NbVals as Integer) as Variant
   
   
   DataRange is a range of cells containing numbers; NbVals is the number of largest values in dataRange with which the mean will be calculated.
   
   Thank you for your help ! Have a great day

 

[Joe4]

Please do not expect us to complete your homework/assignments for you - that is not what this board is intended to do.

Your best bet is to post what you have done/tried so far, and we may be able to give you tips to point you in the right direction.

 

[Cloclo1990]

I have done this, but it is not complete at all...



Option Explicit


Function MeanOfLargest(DataRange As Range, NbVals As Integer) As Variant


'Declare Variables
Dim NbVals As Integer, i As Integer


'Set Range from which to dertermine the largest Value
MsgBox "The Range is: " & Range("A1").End(xlDown).Row
Cells(1, 3).Value = Range("A1").End(xlDown).Row


'Ask User input
NbVals = InputBox("Enter a number of values from which the mean will be calculated: ")
Cells(2, 3).Value = NbVals


'Ensure Number Entered >= to 1


Do Until NbVals >= 1
MsgBox "ERROR: Parameter 2 out of Range"
NbVals = InputBox("Re-Enter a new number of values from which the Mean will be calculated: ")
Loop


'Ensure Number Entered <= Range
Do Until NbVals <= Cells(1, 3).Value
MsgBox "ERROR: Parameter 2 out of Range"
NbVals = InputBox("Re-Enter a new number of values from which the Mean will be calculated: ")
Loop


'Show N Largest Values in the array
For i = 1 To NbVals
Cells(1 + i, 5).Value = i
Do Until Cells(1 + i, 5).Value = ""
Cells(1 + i, 6).Value = "=LARGE (A:A,{1:i})" !!!!!!!??????
Loop
Next i




'Function Execution
MeanOfLargest = ...?


'Display Message Box with mean result to user
MsgBox "The Mean is: " & MeanOfLargest


End Function




'Program to Clear Data before new execution


Sub ClearSheet()


Dim i As Integer


Cells(1, 3).Value = ""
Cells(2, 3).Value = ""
Cells(2, 9).Value = ""


For i = 1 To 10000
Cells(1 + i, 5).Value = ""
Cells(1 + i, 6).Value = ""
Next i


End Sub