Choosing a Formula Based on a Value
 

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?

Choosing a Formula Based on a Value
 

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

--
__________________________________
HTH

Bob

"Rob Fox" <Rob wrote in message
...
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?

Choosing a Formula Based on a Value
 

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.
--
Gary''s Student - gsnu200831


"Rob Fox" wrote:

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?

Choosing a Formula Based on a Value
 

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.


In article ,
Rob Fox <Rob wrote:

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?

Choosing a Formula Based on a Value
 

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

"JE McGimpsey" wrote:

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.


In article ,
Rob Fox <Rob wrote:

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?