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

"T. Valko" wrote:

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(D1:D10="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 C1:D10, F1:F10 it
will do what you want.

--
Biff
Microsoft Excel MVP


"rk0909" wrote in message
...

thanks for all the posts. The problem at hand is:

Col C Col D Col F
6 India 100
6 Germany Need a formula which averages all of col F with
6
in Col C and Germany in Col D
3 China 50
6 Germany Need a formula which averages all of col F
with
6 in Col C and Germany in Col D

so i was thinking if there was a way to identfy cells with formula vs.
constant i could use sumifs to avoid circularity.

thanks,

RK
"Bernd P" wrote:

Hello,

You can use Get.Cell:
http://sulprobil.com/html/get_cell.html

Regards,
Bernd
.



.