

TK Solver: Converting Excel Formulas



Many customers are interested in converting their existing
spreadsheet files to TK Solver files. One reason is that TK files can be run
with RuleMaster, effectively distributing those models worldwide from a single
server. Application developers can
maintain their code more easily in TK Solver and they don’t have to send new
files to their end users every time they make a change.
Users also benefit from TK’s list solving,
backsolving, and unit conversion capabilities – features not available in
Excel. And they can combine their formulas with those from the existing
TKbased applications such as Roark’s Formulas, Heat Transfer, Queuing Theory,
and Financial Management. 

The conversion process is simple. Highlight and copy the spreadsheet cells to be converted.
Then switch to TK Solver and paste into the Rule Sheet. For example, if cell C4 contains the formula =SQRT(A4^2+B4^2), and
that cell is copied to TK, the rule will appear as C4 = SQRT(A4^2+B4^2). The
process can be improved by first using the MathLook for Excel utility to
provide more meaningful names to the spreadsheet cells. The cell names are
automatically transferred to TK with the formulas. 



Many spreadsheets contain tables of calculations based on
copied formulas. For example, if cell B4 in the above example is copied and
pasted to the range B5.B20, the same formula will simply be repeated sixteen
times with the row number changing in each cell. If that column were then
copied and pasted into TK, you would have sixteen rules. The first five are
shown below.


It is unlikely that this is the approach you would want to
take. You would suddenly have 45 new variables in your TK model. There are two
better options.You can list the rule
once and use TK’s List Solver to solve it repeatedly over a range of inputs. You
can create a procedure function that loops through the range of inputs, solving
for the new value of C at each step. The best choice depends on the
requirements of the end user. Procedure functions are used when more automation
is desirable. List Solving gives the end user more control.For RuleMaster applications, procedure
functions are the best choice. 

Another conversion issue deals with incompatibility of
builtin functions. For example, if cell G10 contains the formula =ROUND(F10,2)
and F10 has the value 1.23456, Excel reports the value 1.23.
If that formula is copied to TK, the result
will be 2. This is because the ROUND function is defined differently.
The correct TK translation would be G10 =
ROUND(F10,.01). Here is a listing of
incompatible functions: CEILING, COUNT, DATE, FLOOR, ROUND, TIME, VALUE. 

Excel includes many functions that are not available as TK
builtin functions. For example, the Excel function SQRTPI returns the square
root of the product of a number and the constant pi. In such cases, an
equivalent TK function must be made available on the Function Sheet. A
collection of functions has already been created and is available through the
UTS web site. The functions are stored in a single TK file that can be merged
into your TK applications as required. 

The Excel function IF is a special case.
Excel handles conditions through the use of
the IF function. For example, the Excel formula =IF(B3<B4,100) will return
100 if B3 is less than B4, otherwise it will return the word FALSE. There is a
second usage which returns a second value if the condition is false. The
formula =IF(B3<B4,100,80) will return 80 if B3 is not less than B4.
TK provides an IF THEN ELSE construct which
is not compatible with Excel’s function. To make the translation possible, two
TK functions are provided in the collection on our website. The TK Function IF
assumes three arguments, and performs the equivalent of TK’s IF THEN ELSE
construct. Function IF1 assumes the simpler case with only two arguments. When
you copy formulas from Excel, check the resulting TK rules. If any have an IF
function with only two arguments, change the IF to IF1 and it will work just as
it did in Excel. 

More advanced spreadsheets with multiple worksheets and VBA
code are probably not good candidates for direct conversion to TK. In these
cases, it has been my experience that starting from the initial flowchart and
building the TK model “from scratch” is most efficient. 

If you discover any other shortcuts in converting
spreadsheets to TK Solver, I would appreciate hearing from you. 

 Todd Piefer, UTS Project Manager 
