Is there a way to check which cells are specifically used in an Excel formula?

I have the following conundrum in Excel:

A1: 1
A2: 2
A3: 3

B1: =average(A1:A2)

In C1:C3, I want Excel to check if the corresponding cell in column A was used in the average calculation, and if it was, display the value of the cell, and, if not, display blank (ex. A1 was used in the average, therefore display the value of A1, which is 1).

Is there a way to do this? I have tried to use the "countif" formula (=countif(b1,a1)), but have not been able to get it work.

Thank you.

>Solution :

You could use something like:

=IF(ISNUMBER(FIND(ADDRESS(ROW(A1), COLUMN(A1), 4), FORMULATEXT(B$1))), A1, "")

and copied down.

However, this would not necessarily prevent a false positive if the formula contained a string which resembled a reference: for example, if the formula in B1 were instead:

=IF(AVERAGE(A1:A2) = 0, 1, "A3")

then the row 3 formula would incorrectly return 3.

More robust would be:

=LET(
    φ, ADDRESS(ROW(A1), COLUMN(A1), 4),
    ω, FORMULATEXT(B$1),
    IF(AND(ISNUMBER(FIND(φ, ω)), 1 - ISNUMBER(FIND("""" & φ & """", ω))), A1, "")
)

Edit: that said, it would require a complex formula indeed to account for all potential false positives: I haven’t dealt with cases such as:

=MAX(Z1, AA1)

which would return a false positive for A1.