MagicCell - Normal

DeptBudgetExpense
A 10000 5200
B 25000 1200
C 0 750
D 35000 100
E 75000 3500
F 0 2400

MagicCell - Normal
MagicCell - Normal

DeptBudgetExpense
A 10000 5200
B 25000 1200
C 0 750
D 35000 100
E 75000 3500
F 0 2400

DeptBudgetExpenseAvailable
A 10000 5200 48.00 %
B 25000 1200 95.20 %
C 0 750 No Budget
D 35000 100 99.71 %
E 75000 3500 95.33 %
F 0 2400 No Budget

Handling Divided By Zero in MagicCell
<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
'... Module 1 display the normal Budget Data
  With Mydb
    .dbQP = "type=Excel| U=1| S=ASPdbBB| D=BudgetSS.xls| ps=-1| TextHolder=Title=MagicCell - Normal| Q=Budget| ni=none"
    .ASPdbNET()
'... Module 2 adds a calculated field of available budget % anticipating 'Divided By Zero' problem
    .dbQP = "type=Excel| U=2| S=ASPdbBB|D=BudgetSS.xls| ps=-1| TextHolder=Title=MagicCell - Divide By Zero| ni=none| nh=t"
    .dbSQL = "SELECT Dept, Budget, Expense, 'dummy' as Available From Budget"
    .dbGridMagicCell = "field=3| macro=<<(#Budget#-#Expense#)/#Budget#:p>>"
    .dbOptions="DBZ=No Budget"
    .ASPdbNET()
  End With
End Sub
</script>
We'll use the MagicCell in a very common situation of Divided by Zero (DBZ). The following example first display the original Excel SS Named Block and then display the same block again with a calculated field of Available Budget = (Budget - Spending) / Budget *100 while anticipating entries of 0 budget but with expense. Different DB handles this problem differently in the SQL level and I have yet to see a good solution. Tornado will handle this one for you without even touching the DB. The masking takes place in the 3rd calculated field where it is displayed in the percent (p) format with the formula of #Budget#-#Expense#)/#Budget# * 100. Names within the # wrappers are field names. There is a comprehensive Equation Express parser within MagicCell to enable Math operations.

Send comments on this topic.
Copyright (c) 1998-2006 ASP-db