# Calc. Automatically change format

This post is a wiki. Anyone with karma >75 is welcome to improve it.

CALC.

I have two cells (A1 and A2); their values are:

A1: 0,1

A2: 12,34567

I want the second cell to be rounded with base showed in the first cell. So i write in A3:

A3: =mround(A2;A1)

and the value shown in A3 is 12,30. Perfect!

Now I'd like to automatically format the number in A3 with the value written in A1. So the result should be 12,3. Varying A1 will automatically change the format of A3. For example, if i write in A1 0,001 I should read in A3 12,345.

thank you!

edit retag close merge delete

Sort by » oldest newest most voted

Best define as many named cell styles as you want to have different number formats for the purpose and apply them with the help of the STYLE function added to the formula in A3 and respective cells or by conditionally formatting the proper cell range. The style part in A3 may look like +CHOOSE(-LOG(A1)+1;"csNumStyle0dec";"csNumStyle1dec";"csNnumStyle2dec")). Or you select 'Formula is' mode for CF and condition (CHOOSE(-LOG(A1)+1;"csNumStyle0dec";"csNumStyle1dec";"csNnumStyle2dec")>0 and 'Defaut' as style to apply under the never met condition. (The appropriate style will already be applied during evaluation of the condition.)

The assignment of styles may, of course, also be controlled by picking the style name from an array with the help of INDEX or OFFSET or VLOOKUP ... instead of using CHOOSE or nested IFs.

more