Mathematical formula not giving same answer in C# as in excel, or calculator

Question

Hi All...

I have been trying to use a formula that is used to work out exclusive VAT in a program that our team is creating. The formula works correctly when used in a calculator or in excel, though gives a different output when used within a function in our program!

here is the function:

function fn_calcVat() 
{
    var vRate = Ext.getCmp('crd_vat_rate').getValue();
    var vTranAmt = Ext.getCmp('crd_tran_amt').getValue();

    if (vRate != '' && vTranAmt != '') 
    {
        alert(Ext.getCmp('vatable').getValue().toString());
        var vAmt = 0;
        if (Ext.getCmp('vatable').getValue().toString() == 'Y')
        {
            vAmt = (vRate / ((vTranAmt / 100) + 1));
            Ext.getCmp('crd_vat_amt').setValue(vAmt.toFixed(2));
            Ext.getCmp('crd_tran_tot').setValue(vTranAmt.toString());
            vAmt = 0;
        }
        else 
        {
            vAmt = ((vRate / 100) * vTranAmt);
            Ext.getCmp('crd_vat_amt').setValue(vAmt.toFixed(2));
            Ext.getCmp('crd_tran_tot').setValue((vTranAmt + vAmt));
            vAmt = 0;
        }                
    }
}

the problem formula is vAmt = (vRate / ((vTranAmt / 100) + 1));

The other formula is working perfectly.

an example input would be 100 with a VAT rate of 14.00, and the expected answer would be a tax amount of 14, though it gives it as 7!!!

We are using a mashup of EXTJS, js and C#...

Any help would be greatly appreciated.

Kind Regards

Nick

Answer 1

I'm guessing that it's due to the difference between integers and floating point numbers.

Try it like this:

vAmt = (vRate / ((vTranAmt / 100.0) + 1.0));

Answer 2

Two things come to mind. First, instead of using 'var', use a decimal or something specific. The reason for this is that the compiler will decide whether you meant integer division or decmial division based on the datatypes of the variables. If it picks integer, it may be doing some unexpected rounding on your behalf. Second thing is to make sure that the variable values are all being fetched correctly from those .getCmp() calls.

Answer 3

You're probably going to falling prey to integer division with the following code:

vAmt = (vRate / ((vTranAmt / 100) + 1));

You should change the formula to the following to avoid confusion:

vAmt = (vRate / ((vTranAmt / 100.0) + 1.0));

However, using the numbers you supplied, plugging in 100 for vTranAmt and 14 for vRate won't have a problem with integer division, but will give you 7:

vAmt = (14 / ((100 / 100) + 1))
     = (14 / ((1        ) + 1))
     = (14 / (2              ))
     = (7                     )

So either your formula is incorrect, or 7 is the correct answer.

Answer 4

Just to explain:

Formula:

vAmt = (vRate / ((vTranAmt / 100) + 1));

Parameters:

vRate = 14
vTranAmt = 100

Substitute:

vAmt = (14 / ((100 / 100) + 1));

Calculus:

vAmt = (14 / (1 + 1));

Calculus:

vAmt = (14 / (2));

Calculus:

vAmt = (14 / 2);

Calculus:

vAmt = (7);

So 7 is the expected answer.

But you can reqrite

vAmt = 100 * vRate / (vTranAmt + 100);

Answer 5

Instead of:

vAmt = (vRate / ((vTranAmt / 100) + 1));

Try:

vAmt = vTranAmt * ((vRate/100));

Edit: oops... you just wanted the VAT not the total amount - vote for Binary's instead...

Answer 6

Are you sure the formula is the same in Excel and it works there? I'm not sure what exactly you're trying to do, but the you're dividing by vTranAmt, so the bigger this value, the smaller the result. That doesn't sound right, if the result is supposed to be some tax amount...

Edit: Most probably you have the vRate and vTranAmt variables switched.

Answer 7

The formula is incorrect, it should be

vAmt = vTranAmt * (vRate / 100.0d);