Ask Your Question
0

Calc. Automatically change format

asked 2015-02-09 11:38:23 +0200

this post is marked as community wiki

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2015-02-09 17:23:58 +0200

Lupp gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-02-09 11:38:23 +0200

Seen: 91 times

Last updated: Feb 09 '15