Excel 2007 : Conditional Formatting: Replace Text

Hi Guys,

Did a search on Conditional Formatting, but could not find anything to solve my issse, hence this post. Please Help

I have a worksheet with alot of different numbers in it, the numbers are sourced via a forumla as per below,
=(('Sheet1'!A1-'Sheet1'!A2)/'Sheet1'!A2)*123+100

This formula generally yields a value ranging between 50 and 150.
I have then setup conditional formatting on the cell to fill in either red, yellow, green based on a value between 50 and 150.
ie. If less than 100 = Red
If less than 110 > 100 = Yellow
If > 110 = Green

What i would like it to also do is, if the value is less than 50, replace it with 50, and if more than 150 replace with 150.
Is this possible ?

Thanks for the help!

Originally Posted by romperstomper

You could use:
=MEDIAN(SLN(Sheet1!A1,Sheet1!A2,Sheet1!A2)*123+100,50,150)

Brilliant, Thanks Works like a charm!

Same here Marvin, learnt something new about the Median and how it works.

Regarding the SLN, SLN is a depreciation formula (straight line)

SLN has 3 variable, Cost, Residual Value & Periods.
E.g. Cost= 20,000 Residual Value= 5,000 Periods=5

formula would be =SLN(Cost, Residual Value, Periods) = SLN(20000, 5000, 5)
Result = 3,000

In Effect a pretty basic formula, ie. (20000 - 5000)/5

For my use i did not use it for depreciation, but to do performance to target calculation.
ie. Target= 10 Actual= 8

Therefore, =SLN(8, 10, 10) (repeat the reference for Residual and Periods)

Hope that post made sense