Excel calculation

Yugaa2010

New Member
Joined
Dec 6, 2019
Messages
3
Office Version
2013
Platform
Windows
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)
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,270
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
Xl only shows the first 15 significant digits, so I'm afraid there is nothing that can be done.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,270
Office Version
365
Platform
Windows
I think it should be 5.84%, rather than 5.8%
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,471
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,582
Office Version
2010
Platform
Windows
=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

New Member
Joined
Dec 6, 2019
Messages
3
Office Version
2013
Platform
Windows
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,471
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
Joined
Dec 6, 2019
Messages
3
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,582
Office Version
2010
Platform
Windows
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".
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top