1

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.

Chrispy243
  • 23
  • 2
  • 1
    INDIRECT is used for turning string cell references "A1" into real ones - `SUM(A1:A3)` = `SUM(INDIRECT("A1:A3"))`. What you want is to evaluate a string formula (like [this](https://stackoverflow.com/q/4471884/6609896)) but that requires VBA to make dynamic. I can imagine you may be able to do something with the `SUMIFS` syntax, as that takes a string comparison operator – Greedo Jan 29 '20 at 17:11
  • Yea, that is the problem and I do know it can be done in VBA but trying to avoid that. I am sure I have done this before a while back but cannot for the life of me remember how. Thanks for taking the time to answer! – Chrispy243 Jan 29 '20 at 18:37

1 Answers1

0
=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)

Rubén
  • 34,714
  • 9
  • 70
  • 166
Greedo
  • 4,967
  • 2
  • 30
  • 78
  • the COUNTIF does work, although seems like a backwards way of making it work. As they say though, if it works, just go with it. :) Thanks! For the IFS version, yea that works too, but I have about 5000 values to validate if they are hitting a specific target and that target could change next week when I have to pull this data and validate again. Just trying to streamline it to make it easy enough for my boss to make it work without breaking it. Thanks for the answer, will be using it! – Chrispy243 Jan 29 '20 at 18:39