Return Cell Reference from Lookup Formula without

问题:

I have a spreadsheet that records daily sales quantities over a time period, and I'd like to calculate average daily sales, but exclude the days before we started selling a product since they all return 0.

I have the below lookup function that returns the value from the last cell in the row containing a value greater that 0.

=LOOKUP(2,1/(F5:L5>0),F5:L5)

Is there a way to return the cell reference of this formula? I think I could then use this with indirect() to create a variable average range.

I've tried a few index match lookups, but this hasn't worked for me because if the last value is a duplicate of an early value, it shortens the total range I want to use.

Any help is greatly appreciated.

回答1:

Use this one:

=CELL("address",INDEX(F5:L5,1,MATCH(2,1/(F5:L5>0),F5:L5)))

and press CTRL+SHIFT+ENTER to evaluate it.

---

In the formula above INDEX(F5:L5,1,MATCH(2,1/(F5:L5>0),F5:L5)) returns cell reference of result and CELL(..) returns address.

Cell reference means that you can use it somehow like this (depends on your needs):

=AVERAGE(F5:INDEX(F5:L5,1,MATCH(2,1/(F5:L5>0),F5:L5)))

with array entry (CTRL+SHIFT+ENTER).

If INDEX returns reference to, say, cell J5, formula evaluates to =AVERAGE(F5:J5)