1

In the formula typed into say cell A2:

=if(A1<0, do something 1, do something 2)

Is it possible to have the condition statement ie A1<0 referenced from another cell (say C1 which contains text "A1<0")?

So the final formula would take the condition from another cell

=if(text in C1 which is the entire condition, do something 1, do something 2)

Currently, the if condition reads everything as a text and therefore doesn't accept it as a conditional.

Forward Ed
  • 1,524
Ashwin Kurian
  • 11
  • 1
    Welcome to Super User. Just a heads up: Google Sheets is off-topic here, those questions can be asked on the Web Apps sister site. You've got several Excel answers, but keep in mind that the two applications are not identical. My answer talks about the EVALUATE function, and I have no idea whether, or how similarly, that is supported in Sheets, or whether Sheets has an alternate solution. In general, the safest bet is to post on the site where you can get an answer specific to your application. – fixer1234 Apr 17 '19 at 08:43
  • Thanks @fixer1234 - Ill translate the equivalent to google sheets. Thanks for letting me know about the sister site. – Ashwin Kurian Apr 17 '19 at 12:18

2 Answers2

2

The IF statement only needs something that returns TRUE or FALSE for the condition. So, if you have a formula in cell C1 that evaluates to TRUE or FALSE, then you can refer to C1 as the condition. You don't have to "copy" that condition into the IF statement.

See the screenshot. C1 has a formula that returns either TRUE or FALSE. Cell C3 has an IF statement that uses only a reference to C1 as the condition.

=IF(C1,"C1 returns True", "C1 returns False")

enter image description here

teylyn
  • 22,743
  • Thanks teylyn - for both the quick turnaround and the cool solution. I realized that I would have to drag this formula down. Ie in cell A2 I would have a similar number that i need to assess. Is there a way to drag this down, other than using a support column? [ie separate the A1 or A2 from the "<0" part?] - I think a support column with A1<0, A2<0 ... may work, but there may be a smarter way i suppose – Ashwin Kurian Apr 17 '19 at 12:11
  • Yes you can drag the formula down. How the formula behaves will depend on how you define the references. You never mention dragging down in your question. Please edit your question to include this additional requirement. Also, add a data sample and the expected result mocked up manually. Then post a comment here, so I can see you have updated your question. – teylyn Apr 19 '19 at 04:44
1

teylyn beat me to that example, which is really the only kind of thing that comes close.

What you're asking is related to a question that has been asked before -- is there a way to store an expression as text and then evaluate it as if it was a formula. That can be done with VBA, using the EVALUATE function, but then you can't embed that as the first parameter of an IF test unless you do something similar to teylyn's example using a helper cell. You might find this related thread interesting: Excel function that evaluates a string as if it were a formula?.

There's actually a limited case where string expressions can be evaluated without VBA. EVALUATE can be used in the Name Manager. Here's an example of using EVALUATE that way. That can be used for a helper cell. I don't have ready access to Excel to test using that within the IF test as the first parameter, but I would be surprised if that worked (Excel typically doesn't support launching complex functions when a simple parameter is expected).

fixer1234
  • 27,486