Prev: Entering Values and Updating Next Empty Cell in a Range
Next: Conditional Formatting Query
From: Bernd P on 7 Mar 2010 03:49
Hello Biff,

> ...
> Not really, but you're making it more complex than need be. Why 2 formulas?
> ...

Try it with one only. INDEX is "cell-bound". If you try it, take care
of possible gaps (empty cells) and of the fact that there might be
less than 5 values, please.

With a VBA function you could just start from the last entry, step
back to the fifth-last-filled, calculate the result and stop.

Regards,
Bernd
From: T. Valko on 7 Mar 2010 12:51
>Try it with one only. INDEX is "cell-bound".
>If you try it, take care of possible gaps
>(empty cells) and of the fact that there
>might be less than 5 values, please.

Array entered...

=IF(COUNT(A2:A100),AVERAGE(SMALL(INDEX(A:A,LARGE((A2:A100<>"")*ROW(A2:A100),MIN(COUNT(A2:A100),5))):A100,ROW(INDIRECT("1:"&MIN(COUNT(A2:A100),4))))),"")

We can also use a non-volatile version but it would be a bit longer.

If the OP only wants the average if there are at least 5 scores...

Array entered...

=IF(COUNT(A2:A100)<5,"",AVERAGE(SMALL(INDEX(A:A,LARGE((A2:A100<>"")*ROW(A2:A100),5)):A100,{1,2,3,4})))

--
Biff
Microsoft Excel MVP


"Bernd P" <bplumhoff(a)gmail.com> wrote in message
news:7ffab2a3-9184-46e2-912a-22cc463c95e9(a)q21g2000yqm.googlegroups.com...
> Hello Biff,
>
>> ...
>> Not really, but you're making it more complex than need be. Why 2
>> formulas?
>> ...
>
> Try it with one only. INDEX is "cell-bound". If you try it, take care
> of possible gaps (empty cells) and of the fact that there might be
> less than 5 values, please.
>
> With a VBA function you could just start from the last entry, step
> back to the fifth-last-filled, calculate the result and stop.
>
> Regards,
> Bernd


From: Bernd P on 8 Mar 2010 14:47
Hello Biff,

Nice one. Also quicker than mine.

I would not call it less complex, though.

The VBA solution which I had in mind:

Function Avg4Last5(r As Range) As Double
Dim i As Long, n As Long
Dim dSum As Double, dMax As Double
i = r.Count
n = 0
dSum = 0#
Do While i > 0 And n < 5
If Not IsEmpty(r(i)) Then
If r(i) > dMax Or n = 0 Then
dMax = r(i)
End If
dSum = dSum + r(i)
n = n + 1
End If
i = i - 1
Loop
Select Case n
Case 5
Avg4Last5 = (dSum - dMax) / 4#
Case 0
Avg4Last5 = CVErr(xlErrNum)
Case Else
Avg4Last5 = dSum / n
End Select
End Function

With about 200 rows this VBA is quicker than both worksheet function
approaches. Not that I think golfers play that many rounds - I just
think this VBA function is easier to use and to understand.

Regards,
Bernd
From: T. Valko on 8 Mar 2010 19:00
>I just think this VBA function is easier to use and to understand.

And, because I'm not much of a programmer, I think formulas are easier to
use and understand!

--
Biff
Microsoft Excel MVP


"Bernd P" <bplumhoff(a)gmail.com> wrote in message
news:efdcf7db-f97b-4e60-aea9-767196dad596(a)t23g2000yqt.googlegroups.com...
> Hello Biff,
>
> Nice one. Also quicker than mine.
>
> I would not call it less complex, though.
>
> The VBA solution which I had in mind:
>
> Function Avg4Last5(r As Range) As Double
> Dim i As Long, n As Long
> Dim dSum As Double, dMax As Double
> i = r.Count
> n = 0
> dSum = 0#
> Do While i > 0 And n < 5
> If Not IsEmpty(r(i)) Then
> If r(i) > dMax Or n = 0 Then
> dMax = r(i)
> End If
> dSum = dSum + r(i)
> n = n + 1
> End If
> i = i - 1
> Loop
> Select Case n
> Case 5
> Avg4Last5 = (dSum - dMax) / 4#
> Case 0
> Avg4Last5 = CVErr(xlErrNum)
> Case Else
> Avg4Last5 = dSum / n
> End Select
> End Function
>
> With about 200 rows this VBA is quicker than both worksheet function
> approaches. Not that I think golfers play that many rounds - I just
> think this VBA function is easier to use and to understand.
>
> Regards,
> Bernd


First  |  Prev  | 
Pages: 1 2 3
Prev: Entering Values and Updating Next Empty Cell in a Range
Next: Conditional Formatting Query