Formula vs. Constant

[T. Valko]

Not sure I'm following you.

This is not a problem if you put the formula **outside** of the referenced
ranges. For example, this array formula** :

=AVERAGE(IF(C1:C10=6,IF(D110="Germany",F1:F10)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

As long as you don't enter that formula anywhere within C110, F1:F10 it
will do what you want.

 

[rk0909]

thanks again. However, this formula needs to be applied in the range itself
and hats why I am getting a circular reference. This needs to be applied to
a large matrix so I wanted to do it within the range itself and not create a
separate range.
thanks again for prompt responses.
RK

 

[Joe User]

thanks again. However, this formula needs to be
applied in the range itself and hats why I am getting
a circular reference. [....]

.....And you want the average formula to be in a cell somewhere in the middle
of, say column F?

Would one of the following approaches work for you?

Suppose the data are in rows 1 through 100, and you want the average formula
in row 50. Then try either of the following array formulas [*]:


1.
=average(if(c1:c49=6,if(d1:d49="Germany",f1:f49)),
if(c51:c100=6,if(d51:d100="Germany",f51:f100)))

The ranges should adjust automagically if you insert or delete rows above
and below the cell with this formula.


2. Under Tools > Options > Calculation (in Excel 2003), set the Iteration
option and Maximum Iterations to 1. Then:

=average(if(c1:c100=6,if(d1:d100="Germany",f1:f100)))


Again, an array formula is committed by pressing ctrl+shift+Enter instead of
just Enter. You should see curly braces around the entire formula in the
Formula Bar, viz. {=formula}. But you should not type the curly braces
yourself. If you make a mistake, select the cell and press F2, make
modifications if any, then press ctrl+shift+Enter.


----- original message -----

 

[Lori Miller]

In response to your questions...

TEXTREF is like INDIRECT but also evaluates expressions
in RC notation. The idea is to change 1 to 0 and evaluate:
IF(0,IsFormula,A1) for use in the second argument of Get.Cell.
Since null arguments evaluate to 0 you can omit the 0 in IF().

Similarly the second approach evaluates:
choose(2,Get.Cell,48,A1) and choose(3,Get.Cell,48,A1)
for use in both Get.Cell arguments.

As you probably know for debugging you can open an Excel4
Macro sheet and enter these expressions using the formula
wizard then press ctrl+` to change to values.

Another application is for evaluating expressions.
Define a name "Evaluate" to refer to:
=EVALUATE(EVALUATE(REPLACE(GET.CELL(6,TEXTREF("rc")),1,5,"if(")))
then you can try things like:
=IF(1,Evaluate,"sum("&A1&")")
where A1 contains comma separated values.

As Biff says, this is really aimed at users for exposing macro
functionality without udfs. Udfs make more sense from a
developer standpoint:

Function GetCell(Type_Num As Integer, Reference As Range) As Variant
GetCell = ExecuteExcel4Macro("get.cell(" & Type_Num & _
"," & Reference.Address(True, True, xlR1C1, True) & ")")
End Function

 

[rk0909]

this works if there I use only one formula per column. If there are more
than one cells where i need to insert this formula, I get circ reference. Is
there a way to structure the If statement that skips the cells with formulas
or zeros. Then I can average across without getting into the circ problem.
thanks again for all your help.

thanks again. However, this formula needs to be
applied in the range itself and hats why I am getting
a circular reference. [....]

Need a formula which averages all of col F with
6 in Col C and Germany in Col D

....And you want the average formula to be in a cell somewhere in the middle
of, say column F?

Would one of the following approaches work for you?

Suppose the data are in rows 1 through 100, and you want the average formula
in row 50. Then try either of the following array formulas [*]:


1.
=average(if(c1:c49=6,if(d1:d49="Germany",f1:f49)),
if(c51:c100=6,if(d51:d100="Germany",f51:f100)))

The ranges should adjust automagically if you insert or delete rows above
and below the cell with this formula.


2. Under Tools > Options > Calculation (in Excel 2003), set the Iteration
option and Maximum Iterations to 1. Then:

=average(if(c1:c100=6,if(d1:d100="Germany",f1:f100)))


Again, an array formula is committed by pressing ctrl+shift+Enter instead of
just Enter. You should see curly braces around the entire formula in the
Formula Bar, viz. {=formula}. But you should not type the curly braces
yourself. If you make a mistake, select the cell and press F2, make
modifications if any, then press ctrl+shift+Enter.


----- original message -----

thanks again. However, this formula needs to be applied in the range itself
and hats why I am getting a circular reference. This needs to be applied to
a large matrix so I wanted to do it within the range itself and not create a
separate range.
thanks again for prompt responses.
RK