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..

Improve this question
12
  • 1
    Did you select number field type for your calculated column? This is where you can control the decimal.
    – SharePointer
    Commented May 17, 2017 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 John
    Commented May 17, 2017 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
    Commented May 17, 2017 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
    Commented May 17, 2017 at 10:01
  • @SMerchant and what is the desired results which you get?? you did not get 99999 decimals ?
    – John John
    Commented May 17, 2017 at 11:20

1 Answer 1

Reset to default
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

Improve this answer
5
  • 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 John
    Commented May 17, 2017 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
    Commented May 17, 2017 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 John
    Commented May 17, 2017 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
    Commented May 17, 2017 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 John
    Commented May 17, 2017 at 11:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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