0

I have two formulas that are calculated using the same dataset. One formula calculates the average of the values, while the other calculates an average with a maximum allowed value of 60. I'm wondering if there's an easier way to write the second formula, one where Excel can automatically update the cells used for calculation (relatively) after copy-pasting the formula into another cell (like it would if you copied "SUM(B2:B4)" from B1 to C1, and it gets updated to "SUM(C2:C4)").

Formula 1: AVERAGE(B51,B57)

Formula 2: AVERAGE( MIN(B51,60), MIN(B52,60), MIN(B53,60), MIN(B54,60), MIN(B55,60), MIN(B56,60), MIN(B57,60) ) / 7

Improve this question
3
  • The way you have it now should update when you copy and paste? Is that what you do or don't want?
    – Jonno
    Commented Feb 5, 2016 at 18:36
  • Yes, it is what I want, but what if there are 5 or 15 values? Is there a simple way to write this formula to work with N values? The AVERAGE function will be updated based on how many consecutive values it encounters near the cell it was pasted into. I could write something in VB, but then it's not going to work with Google Sheets.
    – Oran D. Lord
    Commented Feb 5, 2016 at 18:40
  • That makes more sense :)
    – Jonno
    Commented Feb 5, 2016 at 18:41

1 Answer 1

Reset to default
1

Use an array formula.

=AVERAGE(IF(B51:B57>60,60,B51:B57))

Enter with Ctrl+Shift+Enter.

This will replace all numbers in your array that are greater than 60 with 60 and then average them.

Improve this answer
1
  • You may need /(ROWS(B51:B57)*COLUMNS(B51:B57)) at the end of this, if the /7 is important in the original formula :)
    – Jonno
    Commented Feb 5, 2016 at 18:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .