Excel question (on retaining cell format when using formulas)

cltt97

Dear All,

Hopefully something has an idea...

I have an excel spreadsheet with conditional formatting, as I have values ranging from 0.0000012 to 800 and I've put on conditional formatting on the number of decimals.
Now I want to combine values from two columsn in one by using something like
= A1 & " - " & B1 so that I get something like 0.008 - 6.7

the problem is that I am loosing my conditional formatting in this process and what I'm getting is

0.0089632541 - 6.742369871

I know I can use the round function, but since every cell has a different amount of decimals I would get very very old in the process. So I'm wondering is there anything that I can put into the above formula that will make it retain the original number formatting?

Would be ever so grateful if someone had a solution....

Alun

Use the TEXT function on A1 and B1. You can specify conditional formatting as the second parameter.

http://office.microsoft.com/en-001/excel-help/text-function-HP010062580.aspx

cltt97

Alun wrote: »

You can specify conditional formatting as the second parameter.

How would I do that? I have about 6 rules in my conditional formatting. If that worked, it would be brilliant

cltt97

Alun,

I think I have figured it out, it seems to work ok on the cells I have tested. Thanks for the tip!!! I have removed all conditional formatting from the cells and instead have come up with a condition in the formula, which now reads like:

=TEXT(G29,IF(G29>10,ROUND(G29,0),IF(G29>1,ROUND(G29,1),IF(G29>0.1,ROUND(G29,1),IF(G29>0.01,ROUND(G29,2),IF(G29>0.001,ROUND(G29,3),IF(G29>0.0001,ROUND(G29,4),IF(G29>0.00001,ROUND(G29,5),ROUND(G29,6)))))))))&" - "&TEXT(H28,IF(H28>10,ROUND(H28,0),IF(H28>1,ROUND(H28,1),IF(H28>0.1,ROUND(H28,1),IF(H28>0.01,ROUND(H28,2),IF(H28>0.001,ROUND(H28,3),IF(H28>0.0001,ROUND(H28,4),IF(H28>0.00001,ROUND(H28,5),ROUND(H28,6)))))))))

cltt97

Actually,

Just in case anyone ever wants to use this - I have tested this now, and I get some weird error responses (0.08 being rounded to 0.18 for some strange reason), but eventually I realised that when using the if function, I don't need the Text function at all, and then it works just fine and dandy!

Alun

I'm confused as to what exactly your conditional formatting is trying to achieve to be honest. Are you just trying to show the first significant digit after the decimal point?

cltt97

Yes, that's exactly what I wanted, and this works just fine now - your text function got me onto the right trail though, so I'm very grateful for that!