Conditional Formatting Numbers Formulaed as Text

[tobermory]

Hello there,

I have a spreadsheet that I would like to conditional format, it's of survey results. I'd like the user to be able to select between a likert score ("0.00") and a satisfaction percentage ("0.00%"), here are a couple of screenshots.

Obviously, as the numbers are stored as text, I cannot conditional format them, I'd like to do a 3 colour scale on the range. Is there a way to convert the "text" into numbers whilst preserving the format of both text formats depending on the cell in B1?

Thanks ever so much for any response.

 

[etaf]

you can use a value() to convert the text into a number in the conditional formatting

 

[tobermory]

you can use a value() to convert the text into a number in the conditional formatting

Thanks for your reply. I did try this however it converts the percentage back into decimal format.

 

[Fluff]

Why not use
=IF($B$1="likert",Sheet2!B4,Sheet3!B4)

and then use condition formatting to change the display format?

 

[tobermory]

Why not use
=IF($B$1="likert",Sheet2!B4,Sheet3!B4)

and then use condition formatting to change the display format?

THANK YOU!!! Such a simple solution, why couldn't I have thought of that?!

 

[Fluff]

You're welcome & thanks for the feedback.