Excel calculation

[Yugaa2010]

Hello
Can you please help me on this
While calculating this

(1,000,000*5.8%)/12

While using calculator:
I am getting
4866.666666666667
(11 times 6 and last 12th one 7)

My requirement as result in calculator 11 times 6 and 12th as 7 ??

Thanks

But while calculating through Excel it is coming as
Decimal places 10 times 6 and 11 digit as 7)

 

[Fluff]

Hi & welcome to MrExcel.
Xl only shows the first 15 significant digits, so I'm afraid there is nothing that can be done.

 

[Tetra201]

...While calculating this

(1,000,000*5.8%)/12

While using calculator:
I am getting
4866.666666666667

I am getting 4833.3...

 

[Fluff]

I think it should be 5.84%, rather than 5.8%

 

[Tetra201]

The following formula will produce a text string with the desired appearance:

=TEXT(INT(1000000*5.84%/12),"0")&TEXT(MOD(1000000*5.84%/12,1),".000000000000")

 

[joeu2004]

=TEXT(INT(1000000*5.84%/12),"0")&TEXT(MOD(1000000*5.84%/12,1),".000000000000")

IMHO, that works only by coincidence. In general, I agree with Fluff.

Consider the following example:

=1234000*5.78%/12

Excel displays only the first 15 significant digit result 5943.76666666667, rounded.

The calculation in VBA using type Decimal can return the 16 significant digit result 5943.766666666667, rounded. The result with the full precision of type Decimal is 5943.7666666666666666666666667.

But Tetra201's formula returns 5943.766666666666.

The reason is: In Excel, the exact decimal representation of the binary result of 1234000*5.78%/12 is 5943.76666666666,642413474619388580322265625.

So the exact decimal representation of the binary result of MOD(1234000*5.78%/12, 1) is 0.766666666666424,13474619388580322265625.

(I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel formats, rounded.)

Tetra201's formula might or might not agree with Yugaa2010's calculator. It depends on how the calculator performs arithmetic and represents the result. (The two might be different, as is the case in Intel-compatible computers.) 64-bit binary floating-point is only of several alternatives that are commonly used in modern calculators.

The Win7 Calculator displays 5943.766666666667 (16 significant digits) in Standard mode, which agrees with the VBA type Decimal result and differs from Tetra201's formula.

It displays 5943.7666666666666666666666666667 in Scientific mode (32 significant digits), which agrees with the VBA type Decimal result, but with 3 more digits.

 

[Yugaa2010]

The following formula will produce a text string with the desired appearance:

=TEXT(INT(1000000*5.84%/12),"0")&TEXT(MOD(1000000*5.84%/12,1),".000000000000")

Hi Thanks for your reply

But some time my values are dynamic
Now can i use same like 100*24%/12.

Can i do this using VBA?

Thanks

 

[Tetra201]

If you have your numbers in, say, cells A2, B2, and C2, the formula becomes

=TEXT(INT(A2*B2/C2),"0")&TEXT(MOD(A2*B2/C2,1),".000000000000")

As joeu2004 pointed out, its result may or may not agree with your calculator.

 

[Yugaa2010]

IMHO, that works only by coincidence. In general, I agree with Fluff.

Consider the following example:

=1234000*5.78%/12

Excel displays only the first 15 significant digit result 5943.76666666667, rounded.

The calculation in VBA using type Decimal can return the 16 significant digit result 5943.766666666667, rounded. The result with the full precision of type Decimal is 5943.7666666666666666666666667.

But Tetra201's formula returns 5943.766666666666.

The reason is: In Excel, the exact decimal representation of the binary result of 1234000*5.78%/12 is 5943.76666666666,642413474619388580322265625.

So the exact decimal representation of the binary result of MOD(1234000*5.78%/12, 1) is 0.766666666666424,13474619388580322265625.

(I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel formats, rounded.)

Tetra201's formula might or might not agree with Yugaa2010's calculator. It depends on how the calculator performs arithmetic and represents the result. (The two might be different, as is the case in Intel-compatible computers.) 64-bit binary floating-point is only of several alternatives that are commonly used in modern calculators.

The Win7 Calculator displays 5943.766666666667 (16 significant digits) in Standard mode, which agrees with the VBA type Decimal result and differs from Tetra201's formula.

It displays 5943.7666666666666666666666666667 in Scientific mode (32 significant digits), which agrees with the VBA type Decimal result, but with 3 more digits.

Hi
Thanks for reply
I tried using VBA data type as Double but I am getting same results.

Can you suggest how can I make this calculation using VBA ?

Thanks in advance

 

[joeu2004]

I tried using VBA data type as Double but I am getting same results.
Can you suggest how can I make this calculation using VBA ?

.
I do not recommend it. I believe you should accept with the limitations of Excel numbers and type Double in VBA.

That said, for your particular example, you might do the following:

Dim x As Variant, s As String
x = CDec(1000000) * CDec(0.0584) / CDec(12)
s = Format(x, "0.000000000000")
Range("A1") = Chr(39) & s

We do not need to use CDec for the last two operations; in this context, VBA would convert 0.0584 and 12 to type Decimal automagically. But that would not be the case in all contexts. So I show the explicit CDec conversion to ensure reliable type Decimal operations.

As I use Format here, the expression always rounds 12 decimal places, producing a result that has 16 significant digits (rounded) because the integer part has 4 digits.

But that would not produce 16 significant digits for all calculations. I presume your calculator does.

So, more generally:

If Len(Int(x)) > 16 Then s = Format(x, "0.000000000000000E+0") _
Else If Len(Int(x)) = 16 Then s = Format(x, "0") _
Else s = Format(x, "0." & String(16 - Len(Int(x)), "0"))

That seems to match the behavior of Win 7 Calculator in Standard mode. I cannot say whether or not that matches the behavior of your calculator.

The assignment to Excel cell A1 demonstrates one way to avoid losing precision. The leading apostrophe ( Chr(39) ) ensures that the value is stored as text.

But if we reference the cell in an arithmetic formula, Excel would interpret only the first 15 significant digits, replacing any digits to the right with zero. Consequently, for your example, the numeric value would be 4866.66666666666, not even 4866.66666666667.

VBA does not have that limitation. So, CDbl(Range("A1")) results in the binary representation of 4866.66666666667. However, that is still 15 significant digits.

-----

I hope that is useful. If not, please explain why you want or need the full 16 significant digits that matches your calculator. Otherwise, I feel that pursuing this any further is a "fool's errand".