Choosing a Formula Based on a Value

[Rob Fox]

How would I choose between several different formulas in a cell based on a
variable in another cell?


Simplified Example:

Let's say I have three fields, field 'A' has a number, field 'B' is our
variable and field 'C' either adds/subtracts or multiplies field 'A' by 5.

How do we get the formula in 'C' to change based on the value in 'B'? Let's
assume for the moment there are more than 7 different formulas so an IF/Then
might be tricky.

Is there anyway to do this without a macro?

 

[Bob Phillips]

=IF(AND(B2=1,B2=2),A2*5,IF(AND(B2=3,B2=4,B2=5),A2+5,A2-5))

 

[Gary''s Student]

Lets use CHOOSE():

=CHOOSE(B1,A1+5,A1-5,A1/5,A1^5,1/A1)
so if B1=1 we add 5
so if B1=2 we subtract 5
so if B1=3, we divide by 5
etc.

 

[JE McGimpsey]

WIthout knowing specifics, it's impossible to tell for sure, but you
could certainly do something like this:

=CHOOSE(B1,<formula1>,<formula2>,<formula3>...)

or group conditions, if possible, as long as the formula didn't get too
long.

My concern is that if you have more than 7 different formulae, auditing
your model to be sure that it's correct can be a nightmare. In my
experience, almost every useful models has at least one error when first
deployed, whether of logic or codeing. The codeing errors are relatively
easy to find. The logic errors are much tougher.

I'd look at breaking out the logic to multiple cells/regions.

 

[Rob Fox]

Thanks so much all for the quick replies... Is there anyway to do this where
the formulas are kept in their own fields? so formula1 is in B2, formula2 is
in b3 ect?
I would need the the references to remain relative.

Thanks again