I have the following data in excel:
(and I'd like to just copy and paste this from Excel, but it doesn't work - why don't they have a SIMPLE working cut-and-paste function?!?)
User T1 T1Max T1MaxTrans T2 T2Max T2MaxTrans T3 T3Max
STKB $1 $100 100 $0.75 $300 500 $0.50 $200
MCQA $2 $50 25 $1 $100 100 $.50
- etc -
Each customer has a different number of transactions each month ("Trans"). The formula for "Charge" is (in metacode)
if Trans <= T1MaxTrans then
if Trans *T1 > T1Max, Charge = T1Max else Charge = Trans * T1
else
if (Trans>T1Max AND Trans<=T2Max) then
if (Trans-T1MaxTrans) * T2 > T2Max then Charge = T1Max + T2Max else Charge = T1Max + (Trans-T1MaxTrans) * T2
else
if IsNull(T3Max), Charge = T1Max+T2Max+(Trans-T2MaxTrans)*T3
else
if (Trans-T2MaxTrans)*T3 > T3Max then Charge = T1Max + T2Max +T3Max else Charge = T1Max + T2Max + (Trans-T2MaxTrans)*T3
I tried loading my data as a crosstable, and got a new table TransBill, with three fields: User, T1, and Data, e.g.
User T1 Data
STKB T1 $1
STKB T1Max $100
STKB T1MaxTrans 100
STKB T2 $0.75
- etc -
How do I create my formula? As an output, I want a straight table that shows:
User Monthly Trans Monthly Charge
STKB 732 $516
MCQA 182 $166
- etc -
I'm not sure how to reference the correct "data" field from the TransBill table. Any ideas?
Thanks,
Kevin
