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 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" 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" 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