Is there a way using excel funtions (not UDF) to find if a cell contains a
formula vs. a number (constant).
I looked around the forum but did not find anything. I tried using cell
function and left function but they return the value in the formula itself.
Any help is appreciated.
Regards,
RK

hi
you might consider the goto function.
press F5. click the special button bottom right.
check formula then ok. excel will high light all formulas.
you can do the same with constrants.

regards
FSt1

"rk0909" wrote:

Is there a way using excel funtions (not UDF) to find if a cell contains a
formula vs. a number (constant).
I looked around the forum but did not find anything. I tried using cell
function and left function but they return the value in the formula itself.
Any help is appreciated.
Regards,
RK

thanks but i need a function so i can use it in a formula setting.
regards,
RK

"FSt1" wrote:

hi
you might consider the goto function.
press F5. click the special button bottom right.
check formula then ok. excel will high light all formulas.
you can do the same with constrants.

regards
FSt1

"rk0909" wrote:

Is there a way using excel funtions (not UDF) to find if a cell contains a
formula vs. a number (constant).
I looked around the forum but did not find anything. I tried using cell
function and left function but they return the value in the formula itself.
Any help is appreciated.
Regards,
RK

"rk0909" wrote:
thanks but i need a function so i can use it
in a formula setting.

I don't believe there is any Excel function that distinguishes between a
formula and a constant in another cell.

But then again, I cannot imagine why you would need to make that distinction
in an Excel formula per se, other than some kind of internal validation.

Perhaps if you tell us the real problem you want to solve, we might be able
to offer viable solutions using Excel functions per se.


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

"rk0909" wrote:
thanks but i need a function so i can use it in a formula setting.
regards,
RK

"FSt1" wrote:

hi
you might consider the goto function.
press F5. click the special button bottom right.
check formula then ok. excel will high light all formulas.
you can do the same with constrants.

regards
FSt1

"rk0909" wrote:

Is there a way using excel funtions (not UDF) to find if a cell contains a
formula vs. a number (constant).
I looked around the forum but did not find anything. I tried using cell
function and left function but they return the value in the formula itself.
Any help is appreciated.
Regards,
RK

thanks but i need a function so i can use it in a formula setting.

You can do this without VBA code but it depends on how you intend to use
it/do it.

Do you want to identify cells that contain formulas and highlight them with
conditional formatting? Or, some other use?

Need more details.

--
Biff
Microsoft Excel MVP


"rk0909" wrote in message
...
thanks but i need a function so i can use it in a formula setting.
regards,
RK

"FSt1" wrote:

hi
you might consider the goto function.
press F5. click the special button bottom right.
check formula then ok. excel will high light all formulas.
you can do the same with constrants.

regards
FSt1

"rk0909" wrote:

Is there a way using excel funtions (not UDF) to find if a cell
contains a
formula vs. a number (constant).
I looked around the forum but did not find anything. I tried using cell
function and left function but they return the value in the formula
itself.
Any help is appreciated.
Regards,
RK

"T. Valko" wrote:
i need a function so i can use it in a formula setting.

You can do this without VBA code but it depends on how you
intend to use it/do it.

Biff, regardless of RK's answer, could you explain what you have in mind,
for my edification.


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

"T. Valko" wrote in message
...
thanks but i need a function so i can use it in a formula setting.

You can do this without VBA code but it depends on how you intend to use
it/do it.

Do you want to identify cells that contain formulas and highlight them
with conditional formatting? Or, some other use?

Need more details.

--
Biff
Microsoft Excel MVP


"rk0909" wrote in message
...
thanks but i need a function so i can use it in a formula setting.
regards,
RK

"FSt1" wrote:

hi
you might consider the goto function.
press F5. click the special button bottom right.
check formula then ok. excel will high light all formulas.
you can do the same with constrants.

regards
FSt1

"rk0909" wrote:

Is there a way using excel funtions (not UDF) to find if a cell
contains a
formula vs. a number (constant).
I looked around the forum but did not find anything. I tried using
cell
function and left function but they return the value in the formula
itself.
Any help is appreciated.
Regards,
RK

You can use the Excel macro function GET.CELL.

See this:

http://groups.google.com/group/micro...cf859ecd?tvc=2

--
Biff
Microsoft Excel MVP


"Joe User" <joeu2004 wrote in message
...
"T. Valko" wrote:
i need a function so i can use it in a formula setting.

You can do this without VBA code but it depends on how you
intend to use it/do it.

Biff, regardless of RK's answer, could you explain what you have in mind,
for my edification.


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

"T. Valko" wrote in message
...
thanks but i need a function so i can use it in a formula setting.

You can do this without VBA code but it depends on how you intend to use
it/do it.

Do you want to identify cells that contain formulas and highlight them
with conditional formatting? Or, some other use?

Need more details.

--
Biff
Microsoft Excel MVP


"rk0909" wrote in message
...
thanks but i need a function so i can use it in a formula setting.
regards,
RK

"FSt1" wrote:

hi
you might consider the goto function.
press F5. click the special button bottom right.
check formula then ok. excel will high light all formulas.
you can do the same with constrants.

regards
FSt1

"rk0909" wrote:

Is there a way using excel funtions (not UDF) to find if a cell
contains a
formula vs. a number (constant).
I looked around the forum but did not find anything. I tried using
cell
function and left function but they return the value in the formula
itself.
Any help is appreciated.
Regards,
RK

Hello,

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

Regards,
Bernd

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
.

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
.

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
.



.

"rk0909" wrote:
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 -----

"rk0909" wrote:
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
.



.