SharePoint List JSON Format Column Exponentation

Question

SharePoint List JSON Format Column Exponentation

Yuki365 21

Hi everyone,
I formatted my SharePoint list column using JSON.
I created a json code using pow, but it doesn't have exponential notation unless it has more than 21 digits.

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=pow('@currentField',1)"
}

For example, if it is "100000000000", I want to display "1E + 11".

How can i do this with json formating?

thank you appreciate your help here.

Yuki365 21 Reputation points

2022-03-29T08:37:13.757+00:00

Note: I want to change the display format of a numeric column directly instead of creating another new column.

thank you.
Yi Lu_MSFT 17,626 Reputation points

2022-03-30T02:07:32.53+00:00

Hi @Yuki365
Are the values in your list a multiple of 10? If the value is 16,32..., what is the value you want to get？
Yuki365 21 Reputation points

2022-03-30T06:09:20.2+00:00

Hi, @Yi Lu_MSFT

Thank you for your reply.

The numerical values to be handled are as follows. This may not be a multiple of 10.
The value to be processed indicates the concentration of the chemical substance, and the numerical value from the 10th power to the 13th power is handled.

What I want to achieve is the same as formatting an Excel cell in exponential notation, like an attachment file.

for example:
4120000000000 = 4.12E + 12
3560000000000 = 3.56E + 12

As a side note, I used "POW" for the JSON code because it automatically switches to exponential notation when it reaches the 21st power or higher, so I set the exponent to 1 so that the value does not change. I'm not sure if I can use this function.
In the SharePoint List, it is only necessary to know the approximate number, and it is ideal to be able to write up to two decimal places.

Answer accepted by question author

0 additional answers

Your answer

Yuki365 21 Reputation points

2022-03-29T08:37:13.757+00:00

Note: I want to change the display format of a numeric column directly instead of creating another new column.

thank you.
Yi Lu_MSFT 17,626 Reputation points

2022-03-30T02:07:32.53+00:00

Hi @Yuki365
Are the values in your list a multiple of 10? If the value is 16,32..., what is the value you want to get？
Yuki365 21 Reputation points

2022-03-30T06:09:20.2+00:00

Hi, @Yi Lu_MSFT

Thank you for your reply.

The numerical values to be handled are as follows. This may not be a multiple of 10.
The value to be processed indicates the concentration of the chemical substance, and the numerical value from the 10th power to the 13th power is handled.

What I want to achieve is the same as formatting an Excel cell in exponential notation, like an attachment file.

for example:
4120000000000 = 4.12E + 12
3560000000000 = 3.56E + 12

As a side note, I used "POW" for the JSON code because it automatically switches to exponential notation when it reaches the 21st power or higher, so I set the exponent to 1 so that the value does not change. I'm not sure if I can use this function.
In the SharePoint List, it is only necessary to know the approximate number, and it is ideal to be able to write up to two decimal places.

Answer 1

Hi @Yuki365
In column formatting, we could not use pow and round, so we could not get the value you want and write up to two decimal places directly.

As a workaround, you said "The value to be processed indicates the concentration of the chemical substance, and the numerical value from the 10th power to the 13th power is handled", so I suggest you to use the following code to replace pow:

{  
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",  
  "elmType": "span",  
  "txtContent": "=if(((((@currentField)/10000000000<10,(@currentField)/10000000000 +'E+10',if((@currentField)/100000000000<10,(@currentField)/100000000000 +'E+11',if((@currentField)/1000000000000<10,(@currentField)/1000000000000 +'E+12',(@currentField)/10000000000000 +'E+13')))"  
}

The result will be:

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

SharePoint List JSON Format Column Exponentation

0 additional answers

Your answer