Conditional Formatting with changing multiple criteria

[jhlwilson]

I have an annual percentage that I am looking to apply a conditional format based upon the current month. For example, my annual loss percentage goal is 12%, which breaks down to 1% per month.

I have a cell showing my annual percentage year to date. The data ran is through March so if the cell is equal to or less than 3%, I would like it to color green. If the cell is greater than 3% I would like it to color red.

However, the "3%" will not be static as it will change when I import new data to the report. When April is done, I will then have Jan-April data and the percentage will move to 4%.

What I have done so far is created a drop down data validation cell. In this drop down it displays each month (1/1/2013, 2/1/2013, 3/1/2013, etc...) When I import new data I would select the corresponding month that this data is ran through.

I also created a table using two columns. The first column reflects the drop down box with months. The second column displays the percentage for each month.

Example...
O P
1 Date Loss %
2 1/1/2013 1
3 2/1/2013 2
4 3/1/2013 3
5 4/1/2013 4


I may be way off on my methods of accomplishing this format but wanted to display my efforts so far. I believe at this point I am stuck on what type of formula I would use to match both dates and then compare column P with my year to date percentage that is pulled from my data.

 

[etaf]

is all the data filling columns
so could you counta and then use the value

=IFERROR(LOOKUP(COUNTA(A2:E2),{0,1,2,3,4},{0,0.01,0.02,0.03,0.04}),"invalid")
so we are counting row 2 for any data thats in column A,b,c,d or e
and then depending on the count - we are using the 2nd array to return the value
put that in a cell somewhere-so you can then compare that cell with your current percentage to use as a conditional format
Arrays cannot be used in the conditional format formula