# Excel calculation

#### Yugaa2010

##### New Member
Hello
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)

Last edited by a moderator:

#### Fluff

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

#### Tetra201

##### MrExcel MVP
...While calculating this

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

While using calculator:
I am getting
4866.666666666667
I am getting 4833.3...

• Yugaa2010

#### Fluff

##### MrExcel MVP, Moderator
I think it should be 5.84%, rather than 5.8%

• Yugaa2010

#### Tetra201

##### MrExcel MVP
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")

• Yugaa2010

#### joeu2004

##### Well-known Member
=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

#### Yugaa2010

##### New Member
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")

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

Can i do this using VBA?

Thanks

#### Tetra201

##### MrExcel MVP
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

##### New Member
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
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 ?

#### joeu2004

##### Well-known Member
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:
Code:
``````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:
Code:
``````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".

1,082,139
Messages
5,363,353
Members
400,729
Latest member
Lisa McConachy

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...