Excel's incorrect calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-2070270%22%20slang%3D%22en-US%22%3EExcel's%20incorrect%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070270%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20as%20easy%20as%201.85%20x%20100%20may%20seem%2C%20my%20excel%20keeps%20on%20returning%20a%20value%20of%20185.49.%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20another%20case%2C%2022.18%20x%203108%20is%20returning%2068920.52%20whereas%20it%20should%20be%2068935.44.%3C%2FP%3E%3CP%3ESo%20far%20I%20have%20tried%20to%20resolve%20by%20erasing%20all%20formatting%2C%20adjusting%20decimals%2C%20formatting%20into%20%22Numbers%22%20but%20the%20issue%20still%20exists!%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20there%20is%20any%20way%20to%20resolve%20this%20calculation%20problem%20in%20excell!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2070270%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2070291%22%20slang%3D%22en-US%22%3ERE%3A%20Excel's%20incorrect%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070291%22%20slang%3D%22en-US%22%3Eno%20experience%20how%20do%20I%20average%20a%20column%20of%20numbers%3F%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2070303%22%20slang%3D%22en-US%22%3ERE%3A%20Excel's%20incorrect%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070303%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3EExcel%20is%20calculating%20with%20full%20values.%3CBR%20%2F%3Eyou%20want%20to%20calculate%20with%2022.18%2C%20but%20the%20value%20in%20the%20cell%20is%2022.175%20I%20guess.%20If%20you%20use%20round(22.175%2C2)%20--%26gt%3B%2022.18%20you%20excel%20will%20calculate%20with%20this%20value...%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F932971%22%20target%3D%22_blank%22%3E%40dennisboyko%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2070655%22%20slang%3D%22en-US%22%3ERE%3A%20Excel's%20incorrect%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070655%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20seems%20about%20right%2C%20however%20plugging%20it%20(Round)%20manually%20every%20time%20will%20be%20time%20consuming%20and%20it's%20not%20working%20across%20the%20board%20i.e.%2C%20it%20works%20in%20some%20sells%20and%20it%20changes%20value%20in%20another!.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20reinstalling%20Excel%20resolve%20this%20issue%20although%20it's%20evident%20the%20issue%20relates%20to%20formatting%20and%20decimals%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F932980%22%20target%3D%22_blank%22%3E%40SchwartauExtra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All,

 

So as easy as 1.85 x 100 may seem, my excel keeps on returning a value of 185.49. 

In another case, 22.18 x 3108 is returning 68920.52 whereas it should be 68935.44.

So far I have tried to resolve by erasing all formatting, adjusting decimals, formatting into "Numbers" but the issue still exists!

Please let me know if there is any way to resolve this calculation problem in excell!!

5 Replies
no experience how do I average a column of numbers??

Hi,
Excel is calculating with full values.
you want to calculate with 22.18, but the value in the cell is 22.175 I guess. If you use round(22.175,2) --> 22.18 you excel will calculate with this value...


 @dennisboyko 

This seems about right, however plugging it (Round) manually every time will be time consuming and it's not working across the board i.e., it works in some sells and it changes value in another!.

 

Will reinstalling Excel resolve this issue although it's evident the issue relates to formatting and decimals? 

 

@SchwartauExtra 

@zink1989 Not need to reinstall. Perhaps the picture below helps.

Screenshot 2021-01-18 at 13.48.50.png

Thanks a lot @Riny_van_Eekelen, i have also found a new method as follows: 

 

In the excel file, go to File, Options, Advanced, scroll down to "when calculating this workbook" and select (tick) "Set Precision as Displayed". This will ultimately resolve the issue related to decimals and super accurate calculations.

 

Kindest regards,

Z

 

@Riny_van_Eekelen