Formula for Value-Combination (FIXED, LEN) doesn't work with values with changed decimal places

%3CLINGO-SUB%20id%3D%22lingo-sub-2149535%22%20slang%3D%22de-DE%22%3EFormula%20for%20Value-Combination%20(FIXED%2C%20LEN)%20doesn't%20work%20with%20values%20with%20changed%20decimal%20places%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2149535%22%20slang%3D%22de-DE%22%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20wrote%20a%20formula%20to%20combine%20a%20value%20and%20its%20standard%20deviation%20in%20one%20cell.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ECell%20A1%3A%202%2C945%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ECell%20B1%3A%200.003%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3Eformula%3A%20%3DFIXED(A1%3B(LEN(B1)-2))%26amp%3B%22(%22%26amp%3B(B1*(10%22%20(LEN(B1)-2))%26amp%3B%22)%22)%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EResult%3A%202%2C945(3)%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EIn%20my%20area%20of%20work%20this%20way%20of%20adding%20the%20standard%20deviation%20with%20paranthesis%20to%20the%20end%20is%20common.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThe%20formula%20works%20fine%20for%20unchanged%20numbers.%20When%20i%20actively%20change%20the%20decimal%20places%20(like%20reduce%20it%20to%203%20places)%2C%20the%20formula%20recognizes%20the%20length%20of%20the%20starting%20number.%20The%20result%20is%20shown%20below%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EA1%3A%200.237%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%5Bedit%3A%20Values%20in%20A1%20are%20also%20reduced%20to%20lower%20decimal%20places%3A%200.237000961170212%5D%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EB1%3A%200.00244365421481958%20--%26gt%3B%203%20decimal%20places%20--%26gt%3B%200.002%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EResult%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E0.23700096117021200(244365421481958)%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EDesired%20result%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E0.237(2)%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThis%20is%20a%20direct%20copy%20of%20my%20reddit%20post%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.reddit.com%2Fr%2Fexcel%2Fcomments%2Flms79r%2Fformula_for_valuecombination_fixed_len_doesnt%2F%3Futm_source%3Dshare%26amp%3Butm_medium%3Dweb2x%26amp%3Bcontext%3D3%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.reddit.com%2Fr%2Fexcel%2Fcomments%2Flms79r%2Fformula_for_valuecombination_fixed_len_doesnt%2F%3Futm_source%3Dshare%26amp%3Butm_medium%3Dweb2x%26amp%3Bcontext%3D3%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2149535%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2155514%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20Value-Combination%20(FIXED%2C%20LEN)%20doesn't%20work%20with%20values%20with%20changed%20decimal%20places%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2155514%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972366%22%20target%3D%22_blank%22%3E%40MrPopannes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%20%3DFIXED(ROUND(A1%2C3)%2C3)%26amp%3B%22(%22%26amp%3B(ROUND(B1%2C3)*1000)%26amp%3B%22)%22%3C%2FP%3E%3CP%3ENote%20my%20US%20system%20uses%20commas%20as%20separators%20rather%20than%20semicolons.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I wrote a formula to combine a value and its standard deviation in one cell.

Cell A1: 2.945

Cell B1: 0.003

formula: =FIXED(A1;(LEN(B1)-2))&"("&(B1*(10^(LEN(B1)-2))&")")

Result: 2.945(3)

In my area of work this way of adding the standard deviation with paranthesis to the end is common.

 

The formula works fine for unchanged numbers. When i actively change the decimal places (like reduce it to 3 places), the formula recognizes the length of the starting number. The result is shown below:

A1: 0.237

[edit: Values in A1 are also reduced to lower decimal places: 0.237000961170212]

B1: 0.00244365421481958 --> 3 decimal places --> 0.002

 

Result:

0.23700096117021200(244365421481958)

desired result:

0.237(2)

 

This is a direct copy of my reddit post:
https://www.reddit.com/r/excel/comments/lms79r/formula_for_valuecombination_fixed_len_doesnt/?utm_so...

1 Reply

@MrPopannes 

 

Try this: =FIXED(ROUND(A1,3),3)&"("&(ROUND(B1,3)*1000)&")"

Note my US system uses commas as separators rather than semicolons.