Conditional Formatting with changing multiple criteria

jhlwilson

New Member
Joined
Apr 23, 2013
Messages
1
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,627
Office Version
  1. 365
Platform
  1. MacOS
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,707
Messages
5,833,242
Members
430,199
Latest member
Petty queen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top