Use an operator in a cell reference?

[ryan.gillies]

Hi everyone, I'm trying to create a formula in Excel and completely stuck!

Assume I have a table with the following values in the following cell references:

A1: 10
A2: >
A3: 100

I want to be able to write the following statement:

=IF(A1 & A2 & A3,"Yes","No")
 
[I]Which would read as...[/I]
 
=IF(10>100,"Yes","No")

I've tried searching for an answer to this, but don't really know what it is I need to search for to find it.

I have to use it as a formula rather than in VBA, as I plan to use it in a conditional formatting function.

Can anyone tell me if this is possible?

 

[NBVC]

Assuming your values are in A1, A2 and A3 as posted, then go to Insert|Name|Define and enter new name: Evaluation and in the refers to field enter =Evaluate(A1&A2&A3)

Then in A4 enter: =Evaluation

The formula can be copied across the columns in a relative manner...

 

[ryan.gillies]

Hi NBVC

In the end I bodged it by using nested IF's to look for either ">" or "<" (the only two operators I'm using) and then compare the cells accordingly.

What you've suggested seems interesting though, I've never used the Evaluate function, so I'll have a play with it.

Many thanks.