Forum Discussion

Naturelover07's avatar
Naturelover07
Copper Contributor
May 25, 2019
Solved

Using ROUND with AVERAGE and LARGE within a formula.

This is the formula to which I would like to add ROUND so that the average is rounded up/down.  This formula averages the last 5 scores of the player, skipping the weeks that the player has not playe...
Excel Online
  • PeterBartholomew1's avatar
    PeterBartholomew1
    May 26, 2019

    Naturelover07 

    I hadn't realised it was a running value rather than a terminal value you are looking for.

    I have implemented the AVERAGE in your workbook without adding any defined names.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 25, 2019

Naturelover07 

=IF(COUNT(Scores)>=5,

ROUND(

AVERAGE(

INDEX(Scores, LARGE(IF($K$5:K25<>"",ROW($K$5:K25)-ROW($K$5)+1),5)) : K25 ),

0 ),

"")

 

I am not sure I under the significance of K25 in a range that goes to K38?

Naturelover07's avatar
Naturelover07
Copper Contributor
May 26, 2019

PeterBartholomew1 

 

Thanks Peter. Using your changes to the formula doesn't give me an average but a blank.   The reference to K25 was due to my cursor being on that line when I copied the formula. I will try to upload my sheet for you to have a full view. 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 26, 2019

    Naturelover07 

    I hadn't realised it was a running value rather than a terminal value you are looking for.

    I have implemented the AVERAGE in your workbook without adding any defined names.

    • Naturelover07's avatar
      Naturelover07
      Copper Contributor
      May 26, 2019

      PeterBartholomew1 

       

      Disregard my last message, I realized that I was seeing only part of the formula.  I am very grateful for your help.

       

    • Naturelover07's avatar
      Naturelover07
      Copper Contributor
      May 26, 2019

      PeterBartholomew1 

       

       Thanks! It works well. 

      Can you explain how you got the cells in that particular column to average without having the AVERAGE formula in each cell?  I will have to implement it in 45 other worksheet.

       

Resources