removing lowest and highest values

[maco]

what I need for excel to automatically remove the highest and lowest TOTAL POINTS and create an average "Speed Rating" of the remaining 3 scores.

Is it possible to get Excel to do this?

[DonkeyOte]

Perhaps: =TRIMMEAN(F2:F6,0.6)

[maco]

that dosent seem to work... what does the 0.6 do in that formula? its giving me the same outcome as doing a simple =average formula and thats not what I want... I want it to remove the highest and lowest total points and give an average if the middle three remaining total points

[squiggler47]

It just happens that the average of all 5 values is the same as the average of the middle 3 values!

0.6 is 3/5ths which just averages the middle 3 numbers out of 5

=(SUM(F2:F6)-MIN(F2:F6)-MAX(F2:F6))/(COUNT(F2:F6)-2)

will demonstrate this

[windknife]

Perhaps:

=(SUM(F2:F6)-MAX(F2:F6)-MIN(F2:F6))/(COUNT(F2:F6)-2)

[maco]

Oh right, I never thought of it being the same outcome...

Does any one know if its possible to get excel to look at a different sheet when a different track is selected, you will need to look at the spreadsheet to understand what im talking about...

at the moment its looking at the BV sheet for the data, but if I want it to look at a different set of data for another track would that be possible

[squiggler47]

assuming your sheets are "track 1" (same as column a)

instead of =VLOOKUP($B2,bv!$A$2:$B$100,2,FALSE)

use
=VLOOKUP($B2,indirect(a2 & "!$A$2:$B$100"),2,FALSE)

[Domenic]

The second argument of TRIMMEAN determines the percentage of data points to exclude from the calculation. So I believe the formula should be as follows...

=TRIMMEAN(F2:F6,2/COUNT(F2:F6))

So, in this example, 2/COUNT(F2:F6) evaluates to 0.4 and excludes 2 data points -- highest and lowest. In this case, it just so happens that using 0.6 as the second argument returns the same result (5 x 0.6 = 3, which is rounded down to the nearest multiple of 2, and again excludes the highest and lowest values).

[maco]

squiggler47 I am getting the error message #REF

do you know why this could be?

I have just noticed it only gives the #REF error when there is more than one word in the sheet name...
E.g - I can use "Track" but I cant use "Track 1"

Is there a way for it to recognise more than just one word in sheet names? or should it do it any way?

[maco]

any help please?
sorry to bump so soon but I need it done asap.

thanks

[Domenic]

Try replacing...

indirect(a2 & "!$A$2:$B$100")

with

INDIRECT("'"&A2&"'!A2:B100")

[maco]

This is what I had first
=VLOOKUP($B2,INDIRECT(A2 & "!$A$2:$B$100"),2,FALSE)

I have tried the following 3 and all give a #value error
=INDIRECT("'"&A2&"'!A2:B100")
or
=VLOOKUP($B2,=INDIRECT("'"&A2&"'!A2:B100"),2,FALSE)
or
=VLOOKUP($B2,INDIRECT("'"&A2&"'!A2:B100"),2,FALSE)

[Domenic]

=VLOOKUP($B2,INDIRECT("'"&A2&"'!A2:B100"),2,FALSE)

The above formula should return the desired result. Can you post a sample workbook showing the error?

[maco]

I would like to thank you all for your help.

The problem has now been "Solved" through the script that Domenic posted last.

Thank you