Theory Question

Stephen_IV said:

after reading some recent posts i have come across some Question.

What does STATIC mean?

STATIC in regards to formulas and cell references?

What does Dereferencing mean?

Click to expand...

Suppose that you want a formula that sums the range A2:A5 even if you insert a row before that range or within that range. The requirement boils down to have the target range unchanged...

INDIRECT in

=SUM(INDIRECT("A2:A5"))

effects the requirement.

Dereferencing means making available the values a reference is housing. In some situations, this is only possible by effecting a second round of evaluation.

"Static" isn't an Excel term per se... it means unchanging.

thank you guys!

Aladin can you give me Example ?



thank you

sorry i ment can you give me an Example of the DeReferencing?

Stephen_IV said:

thank you guys!

Aladin can you give me Example ?



thank you

Click to expand...

Stephen,

There are actually a few threads here you can track down using "dereferencing" and "deferencing" (as often misspelled by me ) as keywords and at microsoft news groups(searchable via Google).

I believe it's Longre who has used the term "dereferencing" while I used to use "second round of evaluation" for the same phenomenon. Here an example I found on my hard disk...

Let D1:S1 house the following:

{"text",3,"text",21,32,3,"text",32,"text",50,9,2,4,"","",""}

where "" stands for an empty cell (not for a formula blank). The following array formula is meant to compute the average of the last 4 numbers:

[1]

{=AVERAGE(INDIRECT(ADDRESS(1,LARGE(IF(ISNUMBER(D1:S1),COLUMN(D1:S1)),{1,2,3,4}))))}

This just returns 4.

If you evaluate this subexpression

INDIRECT(ADDRESS(1,LARGE(IF(ISNUMBER(D1:S1),COLUMN(D1:S1)),{1,2,3,4})))

on the Formula Bar using F9, we see...

=AVERAGE({4,2,9,50})

If we evaluate this with F9, we get...

16.25

which is the expected result the formula in [1] is unable to compute.

The following formulas however give us the expected result...

[2]

=AVERAGE(N(INDIRECT(ADDRESS(1,LARGE(IF(ISNUMBER(D1:S1),COLUMN(D1:S1)),{1,2,3,4})))))

[4]

=AVERAGE(AVERAGE(INDIRECT(ADDRESS(1,LARGE(IF(ISNUMBER(D1:S1),COLUMN(D1:S1)),{1,2,3,4})))))

N in [3] and the additional AVERAGE in [4], a second round of evaluation, dereferences what INDIRECT actually returns for the surrounding AVERAGE.

Aladin

Thank you for your time I appreciate this.