I would like to input a number in a range from another sheet and I don't know how to do it.

The function:

=PERCENTRANK.INC(sheet1!$C$6:$C$**96**;sheet1!$C$6))

For example: The result of this function should be in a range of 10 days. So the range would be sheet1!M6:M16 (not sheet1!M6:M96)

I want input different numbers on the "96" space.

And I have a large matrix so it's impossible to do it manually.

Thank you so much!!

CodePudding user response：

=Average(indirect("sheet1!M6:M"&A1)) in A1 put 96

CodePudding user response：

You may want to replace the range inside the average: =AVERAGE(FILTER(…)) where you can define a range by certain criteria.

edit:

If you want to use the absolute cell reference, see the answer from Zorigt.

Alternatively, you could also use the OFFSET function to define your range starting from a reference cell (e.g. 10 rows for 10 days)

With the filter you could define e.g. from which day to which day and/or any other criteria

CodePudding user response：

Avoid the use of volatile INDIRECT and OFFSET set-ups when a perfectly good non-volatile INDEX set-up is available.

=PERCENTRANK.INC(Sheet1!C6:INDEX(Sheet1!C:C,G3),Sheet1!C6)

CodePudding user response：

If you want dynamic range as per the input you will give better make use of the address function with indirect. indirect takes static cell reference.

=ADDRESS($C$3 6,3) Result: $C$16

=PERCENTRANK.INC($C$6:INDIRECT(ADDRESS($C$3 6,3)),C6) however this function doesn't ignore the values outside the range that you gave.

so to overcome this problem you can check if the range you gave and the current row/cell in the formula is greated then or equal.

=IF(ROW(INDIRECT($D$3))>=ROW(),PERCENTRANK.INC($C$6:INDIRECT($D$3),C6))