Ok, so I am trying to do something I thought was very simple, but it is turning out to be more complicated.
What I am trying to do:
Take a value through an if statement and return 1 or 0. But I want to be able to change the formula by changing values in cells and not editing the formula itself.
Example:
cell A1 = 41%
cell B1 = >
cell C1 = 40%
cell D1 = Formula with calculation
I want to create a formula that will tell me if that 41% is > than 40%, but if I change that > in B1 for a < (in a separate cell outside the cell with the formula) or I change C1 I want it to recalculate.
I have tried the following:
=IF(A1&B1&C1,1,0)
=IF(A1&INDIRECT(B1)&C1,1,0)
=IF(INDIRECT(A1)&INDIRECT(B1)&INDIRECT(C1),1,0)
all of these result in errors and I cannot figure out how to make it work. I am assuming it is taking the > and making it a string instead of a part of the formula.
Thanks for the help.
=COUNTIF( A1, B1&C1 )
... seems to do the trick, although converting C1 to text may give some rounding errors
An alternative would of course be to enumerate all the operations:
=--IFS( B1=">", A1>C1, B1="<", A1<C1 )
And add other operators as you come across them (NB the -- turns TRUE/FALSE into 1/0)