Prev: Entering Values and Updating Next Empty Cell in a Range
Next: Conditional Formatting Query
From: Bernd P on 6 Mar 2010 13:43
Hello,

> ...
> What should happen if there aren't at least 5 scores?
> ...

Does not matter. Array-enter
=AVERAGE(SMALL(A1:A99,ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))

Regards,
Bernd
From: Bernd P on 6 Mar 2010 14:06
On 6 Mrz., 19:43, Bernd P <bplumh...(a)gmail.com> wrote:
> Hello,
>
> > ...
> > What should happen if there aren't at least 5 scores?
> > ...
>
> Does not matter. Array-enter
> =AVERAGE(SMALL(A1:A99,ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))
>
> Regards,
> Bernd

Not most recvent, though.

Regards,
Bernd
From: T. Valko on 6 Mar 2010 14:08
>>What should happen if there aren't at least 5 scores?
>Does not matter.

You must have magical powers. You're so good you can read the OP's mind?

>=AVERAGE(SMALL(A1:A99,ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))

That won't do what the OP asked for.

--
Biff
Microsoft Excel MVP


"Bernd P" <bplumhoff(a)gmail.com> wrote in message
news:9d1c2b5d-8623-4398-9d91-3092169f011c(a)19g2000yqu.googlegroups.com...
> Hello,
>
>> ...
>> What should happen if there aren't at least 5 scores?
>> ...
>
> Does not matter. Array-enter
> =AVERAGE(SMALL(A1:A99,ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))
>
> Regards,
> Bernd


From: Bernd P on 6 Mar 2010 14:24
Hello,

Two steps:

Array-enter into B1:B5:
=INDEX(A1:A99,LARGE(IF(A1:A99<>"",ROW(A1:A99)),ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),
5)))),1)

Then array-enter into C1:
=AVERAGE(SMALL(B1:INDEX(B1:B5,MIN(COUNTA(A1:A99),
5));ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))

But that's quite complex. Maybe better to take a UDF.

Regards,
Bernd
From: T. Valko on 6 Mar 2010 16:56
>But that's quite complex.

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

--
Biff
Microsoft Excel MVP


"Bernd P" <bplumhoff(a)gmail.com> wrote in message
news:53799cc9-5258-4980-a305-aacb241a38d6(a)q21g2000yqm.googlegroups.com...
> Hello,
>
> Two steps:
>
> Array-enter into B1:B5:
> =INDEX(A1:A99,LARGE(IF(A1:A99<>"",ROW(A1:A99)),ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),
> 5)))),1)
>
> Then array-enter into C1:
> =AVERAGE(SMALL(B1:INDEX(B1:B5,MIN(COUNTA(A1:A99),
> 5));ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))
>
> But that's quite complex. Maybe better to take a UDF.
>
> Regards,
> Bernd


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