Excel is not calculating the correct prices

ckmoied01

New Member
Joined
Jan 8, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi, Excel is not calculating the prices correctly.

Dont get if this is due to data formatting or issues with excel formulas.

Any suggestion to fix please ?

Example from Row 1, 1270 * 217,88 should be 276.707,60, but excel calculates 276.713,24

Overall the entire sheet has the same problem.

1704710304080.png


Thanks
ckmoied01
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Just simple multiplacation of the two columns

Also, somehow my sheet uses Userdefined format.
But in any case the value is 99,04 and multiplied with 1270 should be 125.780,80, but excel calculates 125.778,74 instead.

1704713054848.png


1704713131014.png
 

Attachments

  • 1704713022425.png
    1704713022425.png
    16.8 KB · Views: 14
Upvote 0
I'm pretty sure there are more digits than 2 after the coma in column I or there are digits after the coma in column F, or both.
Show us a screenshot of both cells with General formatting (or Standard in german)

Here you can see all the digits in every cell:

Book1
FGHIJ
21,270.0099.04125,780.80
31,270.0099.0383779527559125,778.74
41,269.979200323199.04125,778.74
Sheet1
Cell Formulas
RangeFormula
J2J2=F2*I2
I3I3=J3/F3
F4F4=J4/I4
 
Last edited:
Upvote 0
I'm not sure how the formats work in your language.
When I put 1.270 * 99.04 in excel i get 125,780.80
 
Upvote 0
I am just using German Version of Microsoft........but that should not matter............format is just format in English or German.

in my sheets excel is calculating wrong prices with simple A*B formula .........and getting no clue why
 
Upvote 0
Can you share your file? uploading it to a cloud storage? and we'll have a look.
Or try XL2BB

1704714646799.png

you can download it here
 
Upvote 0
did you try formatting all the cells to Standard? What do you see? I'm sure there are more digits than you see because of the current formatting.
 
Upvote 0
OK, i have just created MiniSheet............and have copied the values of the cells.............yes.........there are 13 decimal places after comma
In original sheet Column "I" values are coming from another sheet, but due to the formatting in source it is picking 13 digits for each value.

How do i fix this ?

sample sheet with wrong calculations.xlsx
DEFGIJKL
2003.02Actual Should beDifference
2103.02.0101.270Stk217,88276.713,24276.707,60-6
2203.02.0201.270Stk99,04125.778,74125.780,802
2303.02.0303.809m176,11670.819,97670.802,99-17
2403.02.04014Stk1.377,5819.286,0719.286,120
2503.02.0503.809m11,0141.926,2541.937,0911
2603.02.0605Stk8.385,2541.926,2541.926,250
27Summe 03.021.176.450,521.176.440,85-10
28
Tabelle1
Cell Formulas
RangeFormula
J21:J26J21=F21*I21
K21K21=1270*217.88
L21:L27L21=K21-J21
K22K22=1270*99.04
K23K23=3809*176.11
K24K24=14*1377.58
K25K25=3809*11.01
K26K26=5*8385.25
J27:K27J27=SUM(J21:J26)
 
Upvote 0
See column J
Book1
DEFGHIJKL
203.02Actual Should beDifference
2103.02.0101270Stk217.8844276707.6276707.60
2203.02.0201270Stk99.03838125780.8125780.80
2303.02.0303809m176.1145670802.996708030
2403.02.04014Stk1377.57719286.1219286.120
2503.02.0503809m11.0071541937.0941937.090
2603.02.0605Stk8385.2541926.2541926.250
27Summe 03.021176440.8511764410
28
Sheet5
Cell Formulas
RangeFormula
J21:J26J21=ROUND(F21,2)*ROUND(I21,2)
K21K21=1270*217.88
L21:L27L21=K21-J21
K22K22=1270*99.04
K23K23=3809*176.11
K24K24=14*1377.58
K25K25=3809*11.01
K26K26=5*8385.25
J27:K27J27=SUM(J21:J26)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top