Help with a calculation! 1

[travis13]

Greetings -

I some help! I am trying to calculate an average that uses some criteria to determine whether or not to include a piece of data.

I have cells A1 thru A4 with the following data: 0%, 100%, 75%, 97%.

I need to come up with the average - but I want to throw out the lowest and the highest - in this case the 0% and the 100%.

Any ideas?

Thanks!

 

[Dreamboat]

=(sum(a1:a4)-sum(max(a1:a4)+min(a1:a4))/(count(a1:a4)-2)

Not sure you need all those parens... Anne Troy

http://www.TheOfficeExperts.com

 

[meintsi]

How about something along the lines of....

=((SUM(B4:B7)-MAX(B4:B7)-MIN(B4:B7))/(COUNT(B4:B7)-2)) *Remember.......
If you don't use your head,
your going to have to use your feet.

 

[meintsi]

don't ya love coincident posts...... *Remember.......
If you don't use your head,
your going to have to use your feet.

 

[Loomah]

I can't do this without code!!
Problem (that I can't work out) is that I would assume a long list that might have more than one occurence of the max or min value of the list so....

Sub mean()
Dim c As Range
Dim stuff As Range
Dim count As Long
Dim tot As Double
Dim av As Double
count = 0
tot = 0
Set stuff = Range("A1").CurrentRegion.Columns(1).Cells

For Each c In stuff
    If c.Value < WorksheetFunction.Max(stuff) And c.Value > WorksheetFunction.Min(stuff) Then
        tot = tot + c.Value
        count = count + 1
    End If
Next
av = tot / count
MsgBox av
End Sub

OR if you would prefer a function for the workbook

Function MyMean(stuff As Range) As Double
Dim c As Range
Dim count As Long
Dim tot As Double

count = 0
tot = 0

For Each c In stuff
    If c.Value < WorksheetFunction.Max(stuff) And _
        c.Value > WorksheetFunction.Min(stuff) Then
        tot = tot + c.Value
        count = count + 1
    End If
Next
MyMean = tot / count
End Function

NOT EXTENSIVELY TESTED
And this is probably the first time I've written a function!
Not bad for 10:30pm!!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Hey Loomah - do-able without code although its a bit of a beast of an array formula:
=SUM((IF(A1:A10=MAX(A1:A10),0,1))*(IF(A1:A10=MIN(A1:A10),0,1))*(A1:A10))/(COUNT(A1:A10)-COUNTIF(A1:A10,MAX(A1:A10))-COUNTIF(A1:A10,MIN(A1:A10)))

Remember to enter with CTRL+SHIFT+ENTER Rgds
~Geoff~

 

[Loomah]

xlbo
I'm impressed (though I haven't tried it I will offline) as I'm still completely lost by array formulae! Any array bigger than 1 or 2 dimensions loses me. It's my blind spot.

Data cubes ok, arrays - nah!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Arrays are a pain till you can get your head round 'em - this si a bit of a hybrid - the stuff after the "/" isn't actually an array. The array itself returns a 1 if the value is neither the min or the max and a 0 if not
this is then multiplied by the cell values so you get
1 * 0 * 0
0 * 1 * 100
1 * 1 * 75
1 * 1 * 97

this is then summed to get 172

this total is then divide by the toal count of numbers - the count of maxes - the count of mins to get your average
Will work for multiple maxes and mins Rgds
~Geoff~

 

[meintsi]

MAX and MIN don't care if the values are duplicated.

Look at what Travis is asking for and see that the above formula works as long as the range is set correctly.

If there's something I'm missing let me know but I just set up an example with 20 different scores watching the intermediate results along the way and I don't see anything wrong with the formula. *Remember.......
If you don't use your head,
your going to have to use your feet.

 

[xlbo]

Yup - checked what Travis is asking for - to "chuck out the lowest and highest"
Now, in a short list, you may only get 1 lowest and 1 highest but consider the following list of numbers:
0
0
100
10
10
0
100
10
10
where the min and max are duplicated
Your formula will not work for this set of numbers as it gives an average of 20 where the average, excluding min and max is clearly 10

Loomah's point was that in a bigger data set, you are more then likely to get a duplicate of either the max or the min or both and he was trying to get round that and me.... I just couldn't let a Chelsea fan have the last word ;-) Rgds
~Geoff~

 

[Dreamboat]

Loomah:

LOL

If a man says something and there are no women there to hear him, is he still wrong?

YES!! Anne Troy

http://www.TheOfficeExperts.com

 

[Loomah]

xlbo
star for
1. the formula
2. TRYING to explain it to ME!!
3. Making me laugh out loud - lukily(?) I ain't got a job so there's no one around to give me strange looks!

dreamboat
I say the wrong thing on all the time so does that make me right?

anyone else
All the above solutions work depending on requirements. It might be that only one max and one min need to be excluded in the same way as Olympic judges work (I think!?). If not then there are the alternatives. So there!

;-)
Finally
LAST WORD If a man says something and there are no women there to hear him, is he still wrong?