Use the value of another cell in an IF formula calculation

[mvrht]

I have a simple IF calculation, however the higher/lower calculation of the formula varies so we calculate this.

How can I insert the value of a cell into an IF formula calculation

STANDARD FORMULA
=if(D2<=A2,"Lower","")

REFERENCE A CELL FORMULA
=if(D2"C2"A2,"Lower","")

So always insert the value(operator) held in column C into the formula calculations


https://docs.google.com/spreadsheets/d/1OuijgOYghEHUHTuegUrTf1zmAaUYz1mU99Wq2yOkxQQ/edit?usp=sharing

I have tried some INDIRECT, CONCAT, INDEX, JOIN options with no joy

 

[StephenCrump]

Based on your post, I was expecting C2 to contain a random string value, e.g. "<=" or ">" or "=", say.

But you actually have a formula in C2: =IF(D2 < A2,"><a2,"><a2,">=","<=") (which looks to me as if it's round the wrong way)

So C2 is redundant in your later formula - you can just test A2 and D2 directly.

I'm not really clear what your range of possible results is, but my suggested starting point would be:

=CHOOSE(SIGN(D2-A2)+2,"Lower","Same","Higher")

If that's not what you're looking for, perhaps you could post some more examples with the desired results?</a2,"></a2,">

 

[mvrht]

Thanks Stephen.

In a nutshell, I just need to insert the value of C2 into an IF calculation.

So when a form is submitted, we establish whether the customer would like the price to increase or decrease. This static value gets inserted into C2 and is the basis for the calculation we use to notify the customer when their target price is hit.

So just like we would use concatenate(e2,d2) to produce £40. I need something similar to produce =if(D3>=A3... but using the value of C2 to insert between D3 and A3

 

[StephenCrump]

Perhaps something like this:

D2: =MyResult

where Name Manager defines MyResult: = EVALUATE(Sheet1!C2&Sheet1!B2&Sheet1!A2)

Copy D2 down the column.

Book1
	A	B	C	D
1	Old price	New price vs old price	New price	Result?
2	2	<=	1	TRUE
3	2	<=	2	TRUE
4	2	<=	3	FALSE
5	2	>=	1	FALSE
6	2	>=	2	TRUE
7	2	>=	3	TRUE
8	2	>	1	FALSE
9	2	>	2	FALSE
10	2	>	3	TRUE
11	2	<	1	TRUE
12	2	<	2	FALSE
13	2	<	3	FALSE
14	2	=	1	FALSE
15	2	=	2	TRUE
16	2	=	3	FALSE
Sheet1

 

[mvrht]

Perhaps something like this:

D2: =MyResult

where Name Manager defines MyResult: = EVALUATE(Sheet1!C2&Sheet1!B2&Sheet1!A2)

Copy D2 down the column.

	A	B	C	D
1	Old price	New price vs old price	New price	Result?
2	2	<=	1	TRUE
3	2	<=	2	TRUE
4	2	<=	3	FALSE
5	2	>=	1	FALSE
6	2	>=	2	TRUE
7	2	>=	3	TRUE
8	2	>	1	FALSE
9	2	>	2	FALSE
10	2	>	3	TRUE
11	2	<	1	TRUE
12	2	<	2	FALSE
13	2	<	3	FALSE
14	2	=	1	FALSE
15	2	=	2	TRUE
16	2	=	3	FALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Not sure what the Name Manager is here, any chance you could make a copy of that Google Sheets and demonstrate?
https://docs.google.com/spreadsheets/d/1OuijgOYghEHUHTuegUrTf1zmAaUYz1mU99Wq2yOkxQQ/copy?usp=sharing