Rank & Average 1

[VincentCrimmins]

Hi,

I'm trying to create a function that will take in 10 values and will return the average of the 3 largest values.

Any ideas? I'm struggling badly!

Thanks,

 

[SkipVought]

Hi,

How are you struggling? Please be specific.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VincentCrimmins]

Sorry, that should say I'm trying to create the function in VBA for Excel.

Thanks...

 

[SkipVought]

What do you have so far?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VincentCrimmins]

So far I can calculate the max value and the average of all the values, but I want to get the average of the top three:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

For HighArrayData = 0 To 9
HighSeg = Range("C" & HighRow - SegmentSize + 1 & ":C" & HighRow)
HighSeg1 = Range("C" & HighRow - SegmentSize * 2 + 1 & ":C" & HighRow - SegmentSize)
HighDiff = (Excel.WorksheetFunction.Max(HighSeg) / Excel.WorksheetFunction.Max(HighSeg1)) - 1
varHighData(HighArrayData) = HighDiff
HighRow = HighRow - SegmentSize
If HighArrayData = SegmentNumber - 2 Then
PeakHigh = Excel.WorksheetFunction.Max(HighSeg1)
End If
Next

HighArrayMax = Excel.WorksheetFunction.Max(varHighData)
HighArrayAvg = Excel.WorksheetFunction.Average(varHighData)


<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

 

[SkipVought]

Why use VB?

Use RANK and AVERAGE.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VincentCrimmins]

The values are sorted by 'date', so I can't sort them by 'value' and then get the average of the top three.

 

[SkipVought]

Who said anything about SORTING?

I am referring to the RANK & AVERAGE spreadsheet functions.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VincentCrimmins]

Sorry, I don't understand. The data is as follows:

A B C
Date Value Average(Top 3 of the Last 10 Values)
23-Jan-09 16.93
22-Jan-09 16.89
21-Jan-09 17.13
20-Jan-09 16.98
19-Jan-09 16.88
16-Jan-09 17.11
15-Jan-09 17.17
14-Jan-09 17.13
13-Jan-09 17.00
12-Jan-09 16.98
09-Jan-09 16.98
08-Jan-09 16.89
07-Jan-09 16.94
06-Jan-09 16.94
05-Jan-09 16.87
02-Jan-09 16.95
01-Jan-09 16.95

 

[SkipVought]

Sorry, I meant to refer your to the LARGE function, rather than the RANK function.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VincentCrimmins]

That's perfect!

Thanks for that Skip, I never came across the LARGE function before.

Regards,