1

I am working on a sharepoint server 2016. and i have a custom list with the following fields:-

enter image description here

i am showing sample of a test data with a test column names, as these info is somehow confidential.

now as shown in the above picture, i am getting a value of -6,999,95999999, which is a result of the following calcualtion:-

(5250 * 12 * 1) - (5833.33 * 12 * 1 )

now using the calculator on my windows machine, the result will be -6999.96 .. so why inside my sharepoint calculated column i am getting -6,999,95999999 instead?? also if i change the number 5833.33 to 4833.33 i will get a correct result without extra decimals as follow:-

enter image description here

which is the result of the following equation :-

(5250 * 12 * 1) - (4833.33 * 12 * 1 )

so is there a way to avoid unwanted decimals and why there are showing on certain scenarios only? keeping in mind that all the numeric fields i am using and all the calculated columns are of type numeric and have automatic number of decimals..

  • 1
    Did you select number field type for your calculated column? This is where you can control the decimal. – SharePointer May 17 '17 at 7:19
  • @SMerchant as i mentioned before all the calculated columns i am using are of type numeric with automatic decimal points... now i do not want to control the decimals ,, as users can enter the number of decimals they like (this is the business requirment).. but i want the result of my equation to be equal to the number i am getting from my windows machine calculator.. or the calculator i use when i was in the high school or the result i get from excel !!! is this something SP can not deliver out of the box !!! and it should show extra 9999 decimals ? – john Gu May 17 '17 at 9:29
  • Yes, users can enter the decimal in the fields, but calculated fields are auto calculating based on the input so you can control the decimal points. I tested your numbers and I get the desired results. – SharePointer May 17 '17 at 9:55
  • It is not SP failing.. it's the CPU .. type 0.1+0.2 in the F12 Console. If the CPU can't calculated then how on earth can an Application like SharePoint running on that CPU? Excel IS a Calculator programmed to deal with all CPU flaws, SP is not a Calculator, not a Database, it is a CMS, so you have to do more correction work yourself... with the TEXT function – Danny '365CSI' Engelman May 17 '17 at 10:01
  • @SMerchant and what is the desired results which you get?? you did not get 99999 decimals ? – john Gu May 17 '17 at 11:20
1

Use the standard TEXT function to format a Number anyway you want

https://support.office.com/en-gb/article/TEXT-function-20d5ac4d-7b94-49fd-bb38-93d29371225c?ui=en-US&rs=en-GB&ad=GB

  • thanks for the reply.. now i do not want to manually format the calculated columns ..now the calculated columns i am using are of type numeric with automatic decimal points.. now i do not want to control the decimals ,, as users can enter the number of decimals they like (this is the business requirment). but i want the result of my equation to be equal to the number i am getting from my windows machine calculator or the result i get from excel !!! is this something SP can not deliver out of the box !!! and it should show extra 9999 decimals ? – john Gu May 17 '17 at 9:31
  • 1
    John, again..and again.. if people say to you to use technology X then (considering their SP experience) there most of the time is no technology Y.. I once wanted SP to turn my Wife into Catherina Zeta Jones... didn't happen... blamed SharePoint for a years... then learned to live with the fact SharePoint is a 15+ year old dinosaur. So I am now learning AWS (Amazon) and hope for a better future – Danny '365CSI' Engelman May 17 '17 at 9:43
  • and what about the problem i am facing ? i do not want to format any numeric values.. as we want users to enter whatever numbers and number of decimals they want .. i am not talking about old/new technology.. now if i do the equation on my solar calculator which goes back to 20 years i will get correct results without 9999 decimals... – john Gu May 17 '17 at 9:47
  • I don't know your wife, so I can tell what your problem is (but by your responses I acknowledge your problems in this area). For SharePoint: What you get is what you get. And it is not SharePoint... it is computers in general... just typ 0.1 + 0.2 in a JavaScript console. Your 20 year old calculator doesn't have a 64 Bit Processor optimized to do important calculations fast and thus gets sloppy on edge-cases – Danny '365CSI' Engelman May 17 '17 at 9:55
  • @Dannu but how i can use the txt functions ,, baring in mind that users can enter whatever number of decimals they want ??? i am not sure how i can control it ? – john Gu May 17 '17 at 11:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.