Icon conditional formatting when cell is result of a concatenated formula

[lbour]

I have percentages in rows A and B. In row C, I have a formula =round((A-B)*100),1)&" pts" In row C, I get the percentage point answer I want rounded to 1 decimal (e.g., -10.3 pts). But I am not sure how to add Conditional Formatting Arrow (Green, Yellow, Red) to row C. Is there a straightforward fix for this?

Thanks!

 

[jtakw]

Hi,

Your formula has turned your result into Text, it makes it more complicated to do CF.

I suggest you removed the &" pts" from you formula, so just:

=ROUND((A1-B1)*100,1)

Then, Format Cell, Custom, enter

0.0 "pts"

Now you should be able to CF easily.

 

[lbour]

Thanks for the feedback! Was hoping to retain the " pts" as it makes the readability of the data easier. Unless you're saying I can apply the CF to the base formula and then concatenate the " pts" while retaining the CF. Have I misunderstood you?

 

[jtakw]

Please re-read my post #2 above regarding Cell Formatting to Custom

You'll Still see -10.3 pts in the cell.

 

[lbour]

Ahh... gotcha! I missed the nuance. Leverage the Custom Format after conditional formatting. Thanks!

 

[jtakw]

Hmmm... Actually you could do the Custom Cell format 1st, but it could be either way.

So your Real cell value is -10.3, that doesn't change, but with the Custom Cell format, you'll see -10.3 pts

 

[Peter_SSs]

@lbour
One thing that will help if you have further questions in the forum is to use the correct terminology.
A, B and C are columns, not rows. Columns are labelled with letters and rows are labelled with numbers.

Another thing that will often help to get the most appropriate answer for you is to update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)