Is there a way to return an executable formula from a lookup table?

I'm trying to select a specific formula to use based on a value in the
record (row) where I would perform the calculation. For example, based on a
code in the data record (row) the desired formula for row 75 might look like:

Code Formula
110 =(L75+(Q75*KPrice))
112 =(L75+(M75*Dprice)+(Q75*Kprice))

There are over 30 possible codes, each with unique calculations.

A workaround is to calculate the results using each possible formula in a
different column withn the row, use IF statements within each formula to set
the value to zero unless the code specified in the formula matches the code
in the data, and summing all the results across all the results columns. It
works, but brute-forcing it this way is very inefficient and slow.

Any Ideas?

If you post all 30, perhaps I can merge them all into a single formula.
Here's a technique you can try.

First, convert your "codes" into a sequential response from 1-30:

LOOKUP(DS9,{40,45,55,60,100,110,112},{1,2,3,4,5,6, 7})
Or put the codes in a chart (codes in column A, numbers in column B
LOOKUP(DS9,$A$1:$A$30,$B$1:$B$30)

Now slip that whole formula into a CHOOSE function and list the "formulas"
in each of the Value1, Value2, Value3, etc...part of the equation.

=CHOOSE(LOOKUP(DS75,$A$1:$A$30,$B$1:$B$30),
(L75+(Q75*KPrice)),(L75+(M75*Dprice)+(Q75*KPrice)) ,L75/2)

....and on.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Mr Machine" wrote:

Is there a way to return an executable formula from a lookup table?

I'm trying to select a specific formula to use based on a value in the
record (row) where I would perform the calculation. For example, based on a
code in the data record (row) the desired formula for row 75 might look like:

Code Formula
110 =(L75+(Q75*KPrice))
112 =(L75+(M75*Dprice)+(Q75*Kprice))

There are over 30 possible codes, each with unique calculations.

A workaround is to calculate the results using each possible formula in a
different column withn the row, use IF statements within each formula to set
the value to zero unless the code specified in the formula matches the code
in the data, and summing all the results across all the results columns. It
works, but brute-forcing it this way is very inefficient and slow.

Any Ideas?

Just put all formulas in a table displaying all possible results and use
VLOOKUP to pick the right answer.
Don't worry about inefficiency or slowness; this will probably involve a few
microseconds.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Mr Machine" <Mr wrote in message
...
Is there a way to return an executable formula from a lookup table?

I'm trying to select a specific formula to use based on a value in the
record (row) where I would perform the calculation. For example, based on
a
code in the data record (row) the desired formula for row 75 might look
like:

Code Formula
110 =(L75+(Q75*KPrice))
112 =(L75+(M75*Dprice)+(Q75*Kprice))

There are over 30 possible codes, each with unique calculations.

A workaround is to calculate the results using each possible formula in a
different column withn the row, use IF statements within each formula to
set
the value to zero unless the code specified in the formula matches the
code
in the data, and summing all the results across all the results columns.
It
works, but brute-forcing it this way is very inefficient and slow.

Any Ideas?

Hi

30 or more will be a way too much, but you may have up to 29 various
expressions in a single formula:

=CHOOSE(ExprNum, Expr1, Expr2, ..., Expr29)

, where EprNum may have values 1/2/.../29

Arvi Laanemets


"Mr Machine" <Mr wrote in message
...
Is there a way to return an executable formula from a lookup table?

I'm trying to select a specific formula to use based on a value in the
record (row) where I would perform the calculation. For example, based on
a
code in the data record (row) the desired formula for row 75 might look
like:

Code Formula
110 =(L75+(Q75*KPrice))
112 =(L75+(M75*Dprice)+(Q75*Kprice))

There are over 30 possible codes, each with unique calculations.

A workaround is to calculate the results using each possible formula in a
different column withn the row, use IF statements within each formula to
set
the value to zero unless the code specified in the formula matches the
code
in the data, and summing all the results across all the results columns.
It
works, but brute-forcing it this way is very inefficient and slow.

Any Ideas?