Forum Discussion

Copper Contributor

Jul 31, 2019

Solved

Using a row value criteria in an averageifs function

Hi, I am trying to create a formula that takes in a range of cells and averages the cells in that range that have a value >=0% and row number >= a number stored in another cell. Is this possible? I am trying to make use of averageif(s) function, but can not figure out how to make use of the row() function nested within. Really appreciate the help!

excel

Formulas and Functions

Twifoo
Aug 01, 2019
nickaho
It would be best if you store your variables in Cells. For example, if the values are in Column A, the Start Row is stored in C2, End Row in C3, and Percent in C4, then the formula to return your desired result would be:
=AVERAGEIF(INDEX(A:A,C2):INDEX(A:A,C3),
">="&C4)

6 Replies

Twifoo
Silver Contributor
Aug 01, 2019
nickaho
It would be best if you store your variables in Cells. For example, if the values are in Column A, the Start Row is stored in C2, End Row in C3, and Percent in C4, then the formula to return your desired result would be:
=AVERAGEIF(INDEX(A:A,C2):INDEX(A:A,C3),
">="&C4)
- nickaho
  Copper Contributor
  Aug 05, 2019
  Twifoo Awesome, thank you so much!
  - Twifoo
    Silver Contributor
    Aug 05, 2019
    Your gratitude delights me!
SergeiBaklan
Diamond Contributor
Aug 01, 2019
nickaho

If your data is in column A and row number to start is in C1, when like

=AVERAGEIF(INDEX(A:A,$C$1):A1000,">=0")

assuming you are within 1000 rows range
- nickaho
  Copper Contributor
  Aug 05, 2019
  SergeiBaklan Thank you! This is perfect
nabilmourad
MVP
Aug 01, 2019
nickaho
My recommendation for you to get an answer is to provide sample data in an Excel file.