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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,413
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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