0

I have a table where I am reporting a range of correlation coefficients which range from -1.0 to +1.0. I want to format them such that if they are plus or minus one or zero, then they are formatted with one decimal place, otherwise they are formatted with two decimal places.

For example if the range is 0.6789 to 1.000 it should be formatted as [+.68,+1.0].

Currently using a combination of CONCATENATE and TEXT function I am able to get [+.68,+1.00].

Can some excel guru out there help?

2
  • What is your current solution?
    – eirikdaude
    Apr 10, 2015 at 20:29
  • Welcome to Super User! Could you please edit your post to include the formula you are using now?
    – Excellll
    Apr 10, 2015 at 20:30

1 Answer 1

1

For lower bound in A1 and upper bound in B1, you can use the following super long formula:

="["&TEXT(A1,"+."&IF(MOD(A1,1)=0,"0","00")&";-."&IF(MOD(A1,1)=0,"0","00"))&","&TEXT(B1,"+."&IF(MOD(B1,1)=0,"0","00")&";-."&IF(MOD(B1,1)=0,"0","00"))&"]"

The key bit is the TEXT function. The format specified in the function includes the condition that if the bound is -1, 0, or 1, it is represented to one decimal place. Otherwise the bound is represented to two decimal places. The test for this condition is MOD(A1,1)=0, which tests whether the number is an integer or not (if true, then integer, otherwise no).

Sample output: enter image description here

1
  • Thank you! That helps! I did not think of the mod function.
    – UditG
    Apr 10, 2015 at 21:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .