Conditional Formatting Nested Formulas

I'm working with a large spreadsheet that has multiple status options.  Each 
status has different criteria for example:

Status	Green	Yellow	               Red
T	<60	>=60,<=80	>80
S 	<20	>=20,<=45	>45
D	<30	>=30,<=55	>55
C	<90	>=90,<=120	>120
B	<30	>=30,<=60	>60
A	<10	>=10,<=40	>40

I would like to apply conditional formats according to the status criteria.  
Is that possible given that each status has a different criterial.  Thank you!

0
Marissa (6)
7/10/2008 8:21:07 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
431 Views

Similar Articles

[PageSpeed] 7

One way to get going here .. 

Illustrated in this sample:
http://www.freefilehosting.net/download/3jddl
Complex Criteria CF.xls

Construct:
In a sheet: x,
Set up the param table below in A2:C7
T 60 80
S  20 45
D 30 55
C 90 120
B 30 60
A 10 40

Then in your data sheet,

Assume the key col values are in B2 down, Statuses (T,S,D etc) in C2 down
Using 4 adjacent cols to the right, say cols D to G
Enter these labels into D1:G1  : Colour, Green, Yellow, Red

Put in, and array-enter, 
ie press CTRL+SHIFT+ENTER [CSE] to confirm each formula:
D2: =INDEX($E$1:$G$1,MATCH(TRUE,ISNUMBER(E2:G2),0))
E2: =MATCH(1,(C2=x!A$2:A$7)*(B2<x!B$2:B$7),0)
F2: =MATCH(1,(C2=x!A$2:A$7)*(B2>=x!B$2:B$7)*(B2<=x!C$2:C$7),0)
G2: =MATCH(1,(C2=x!A$2:A$7)*(B2>x!C$2:C$7),0)

Select D2:G2, copy down to the last row of data. Col D will return the 
required "colour" for each data row. Hide away cols E to G as desired. You 
could then easily point to col D's colour values to conditionally format as 
desired.

Take care to visually see that the top cells D2:G2 are correctly 
array-entered before you copy down. Look in the formula bar, each formula 
should be wrapped with curly braces by Excel: { }. If you don't see the 
curlies, that means it wasn't correctly array-entered. Click inside the 
formula bar, and re-do the CSE.
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
--- 
"Marissa" wrote:
> I'm working with a large spreadsheet that has multiple status options.  Each 
> status has different criteria for example:
> 
> Status	Green	Yellow	               Red
> T	<60	>=60,<=80	>80
> S 	<20	>=20,<=45	>45
> D	<30	>=30,<=55	>55
> C	<90	>=90,<=120	>120
> B	<30	>=30,<=60	>60
> A	<10	>=10,<=40	>40
> 
> I would like to apply conditional formats according to the status criteria.  
> Is that possible given that each status has a different criterial.  Thank you!
> 
0
demechanik (4694)
7/11/2008 1:43:00 AM
Here's a slightly different approach......

Assuming the same table as Max, but in D2:F7 in the same sheet as your data, 
then with status in column A and value in B, select the column(s) you want to 
format and use these conditions

condition 1
=$B1>VLOOKUP($A1,$D$2:$F$7,3,0)*ISNUMBER($B1)
format red

condition 2
=$B1>VLOOKUP($A1,$D$2:$G$7,2,0)*ISNUMBER($B1)
format yellow

condition 3
=ISNUMBER($B1)
format green

"Max" wrote:

> One way to get going here .. 
> 
> Illustrated in this sample:
> http://www.freefilehosting.net/download/3jddl
> Complex Criteria CF.xls
> 
> Construct:
> In a sheet: x,
> Set up the param table below in A2:C7
> T 60 80
> S  20 45
> D 30 55
> C 90 120
> B 30 60
> A 10 40
> 
> Then in your data sheet,
> 
> Assume the key col values are in B2 down, Statuses (T,S,D etc) in C2 down
> Using 4 adjacent cols to the right, say cols D to G
> Enter these labels into D1:G1  : Colour, Green, Yellow, Red
> 
> Put in, and array-enter, 
> ie press CTRL+SHIFT+ENTER [CSE] to confirm each formula:
> D2: =INDEX($E$1:$G$1,MATCH(TRUE,ISNUMBER(E2:G2),0))
> E2: =MATCH(1,(C2=x!A$2:A$7)*(B2<x!B$2:B$7),0)
> F2: =MATCH(1,(C2=x!A$2:A$7)*(B2>=x!B$2:B$7)*(B2<=x!C$2:C$7),0)
> G2: =MATCH(1,(C2=x!A$2:A$7)*(B2>x!C$2:C$7),0)
> 
> Select D2:G2, copy down to the last row of data. Col D will return the 
> required "colour" for each data row. Hide away cols E to G as desired. You 
> could then easily point to col D's colour values to conditionally format as 
> desired.
> 
> Take care to visually see that the top cells D2:G2 are correctly 
> array-entered before you copy down. Look in the formula bar, each formula 
> should be wrapped with curly braces by Excel: { }. If you don't see the 
> curlies, that means it wasn't correctly array-entered. Click inside the 
> formula bar, and re-do the CSE.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads: 15,500, Files: 352, Subscribers: 53
> xdemechanik
> --- 
> "Marissa" wrote:
> > I'm working with a large spreadsheet that has multiple status options.  Each 
> > status has different criteria for example:
> > 
> > Status	Green	Yellow	               Red
> > T	<60	>=60,<=80	>80
> > S 	<20	>=20,<=45	>45
> > D	<30	>=30,<=55	>55
> > C	<90	>=90,<=120	>120
> > B	<30	>=30,<=60	>60
> > A	<10	>=10,<=40	>40
> > 
> > I would like to apply conditional formats according to the status criteria.  
> > Is that possible given that each status has a different criterial.  Thank you!
> > 
0
7/12/2008 1:11:02 PM
Reply:

Similar Artilces:

Solving a formula
I have the following formula that I need Excel to solve for different values of P6 ( I have different values of P6 (P7, P8, P9 (reference cells)). I need the value of x for the different values of P6. Is this possible? It seems like it should be but I can't find out how to make Excel do this. Please help! Thanks. 70000 = P6*((((x/80)*(1/5280))*(2/3))+(((x/40)*(1/5280))*(1/3))) If I am not mistaken, x equals: = 22176000000 / P6 HTH -- Dana DeLouis Win XP & Office 2003 "Anth" <Anth@discussions.microsoft.com> wrote in message news:6196C6C5-FC82-42E2-B6A1-B2...

How to convert old pst file to 2007 format for inc storage capacit
-- Kim http://www.slipstick.com/outlook/ansi-to-unicode.asp -- Russ Valentine "Kim" <Kim@discussions.microsoft.com> wrote in message news:375EEC33-CC22-46F1-87D5-94B6F106FDC3@microsoft.com... > > -- > Kim Hi If you have any problems with pst file, you can try a tool called Advanced Outlook Repair. You can download a free demo version at http://www.datanumen.com/aor/aor.exe . I think it is a useful repair tool for you. It is easy to recover your PST file by using its wizard. It is a powerful tool to recover messages, folders and other objects...

Need help with a formula
I am trying to get Excel to make some calculations for me. This is my first time trying to use Excel and I can't seem to figure out how to tell it what I want it to do. I want it to tell me a percentage rate. I tell it how much I paid for an item and how much I sold the item for. I got the formula to get it to give me the amount of the difference, but how would I tell it to give me that number in the next cell as the % as well? I hope that makes since. For instance, if I bought a car for 500.00, turned around and sold it for 750.00, I've already got it to give me the diffe...

Determine cells that drive conditional formatting?
Example: Cells A1:A4 have conditional formatting set up that states if they are equal to cell A10 they will be highlighted yellow. Is there a way to quickly see what cells drive conditional formatting? In other words, we can use Edit -> Go To Special to see which cells have conditional formatting applied; however, this doesn't show us that A10 is involved. Since there isn't a formula directly involved, we can't trace precedents/dependents. Can we only know that A10 is involved in the formatting of A1:A4 by selecting those cells and going into the conditional format...

Conditional Formatting of Text...
....I know how to apply Conditional Formatting to cells containing numeric data; is it possible to apply it to cells where the result is textual? For example: =if(a1>=5000,"High","Low") In this instance, I would want to conditionally format this cell so that if the result is High, then the text should be bold and red. Thanks in advance. I'm using Excel 2002... Hi click on the cell choose format / conditional formatting choose cell value is equal to ="high" click the format button set your formatting click OK twice Regards JulieD "Birmangirl&quo...

date format into day of week
I have column in excel which show date in format 2009-08-21. Is it possible in new column to get day of week from this data? Is this what you are looking for? -- Rick (MVP - Excel) "Steve" <Steve@ggmmaaiill.com> wrote in message news:euX0IfIJKHA.3928@TK2MSFTNGP04.phx.gbl... >I have column in excel which show date in format 2009-08-21. Is it possible >in new column to get day of week from this data? > > > Is this what you are looking for? =TEXT(A1,"dddd") -- Rick (MVP - Excel) "Steve" <Steve@ggmmaaiill.com> wrote in me...

Localized Header/Footer formatting codes in Excel 2000
Hello, I would like to set the Header/Footer parameters of an Excel worksheet from a VC++/MFC application using formatting codes, for example: PageSetup page = worksheet.GetPageSetup(); page.SetLeftHeader(_T("&D &T")); // print date and time to header The problem is that my Office 2000 is a localized version (Hungarian), and Excel doesn't seem to recognize the &T code, only the localized version, which is &I. This code works fine: page.SetLeftHeader(_T("&D &I")); I recorded a macro, and the generated VBA code also uses the non-localized vers...

IF Formula 01-13-10
I am trying to compare two cells to eachother and populate a third cell with the number that is the GREATER of the two. (IF f26 is greater than G34, populate m22 with value in f26; BUT if g34 is greater than f26, populate m22 with value in g34). Any suggestions? On Jan 13, 10:17=A0am, CrazyConfused <CrazyConfu...@discussions.microsoft.com> wrote: > I am trying to compare two cells to eachother and populate a third cell w= ith > the number that is the GREATER of the two. =A0(IF f26 is greater than G34= , > populate m22 with value in f26; BUT if g34 is greater than...

Formats for video
What are the formats most commonly used for posting video to a personal website? I want to post some holiday video, but would like to maximise the chance that users will be able to view the video without having to download additional software, and without taking too much time or bandwidth. Can you help? thanks, Heidi Heidi <Heidi@discussions.microsoft.com> was very recently heard to utter: > What are the formats most commonly used for posting video to a > personal website? I want to post some holiday video, but would like > to maximise the chance that users will be able to ...

conditional formatting #value!
Hi Is it possible to create a conditional formatting condition that can detect the condition of #VALUE! ? That is NOT the text string but the "error condition". What I am trying to highlight is that certain calculated values have not been correctly calculated on the sheet and need further investigation. Thanks Clive Hi Clive, Use the 'Formula Is' option on the conditional formating dialog and enter the following, assuming the cells is A1. =ERROR.TYPE(A1)=3 You can also use the Go To dialog (CTRL+G), Special... to locate cells with Errors. Cheers Andy Clive Long...

format a timesheet it adds the hrs but need a negative for flex
I have been working on a timesheet for work I've got the format to add the hrs for the day and the total for the week but when the overtime adds on to the flex it want give me a negative it just comes up with ######## can you please help thanks One way to show negative dates/times in excel is to use the 1904 date system. Tools|Options|Calculation tab Be aware that any dates in your worksheet will change by 4 years and a day. But if you're adding hours, how do you get a negative value? juls kartinyeri wrote: > > I have been working on a timesheet for work I've got the...

Filtering out text with conditional formatting
hi there, I have a problem I need help with. I have a worksheet that has column with 13 digit long number in it. I want a formula (or is it conditional formatting) that will highligh the cell if the last 5 digits of the number are not �00000� The number is in text format and needs to stay that way� so I wa thinking there could be come way to examine the text .. I dunno� an ideas greatly appreciated� thanks -- bluebea ----------------------------------------------------------------------- bluebean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2595 View th...

FOrmula to work out pricing rounding up and down
I have a formula in cell that reads "=3DIF(C7>0,"FOC",-C7*1.175)" it turns a negative into a Positive and a Positive FIgure into answer of "FOC" Sometimes it will display a result of =A32 or =A35.50 or whatever the figure when the original answer is a negative. With me so far!! The result which is displayed as a number i need it to round up t the nearest 9.99. For example if c7 is =A34.00 display answer of 9.99 if c7 is =A311.00 display answer of 19.99 if c7 is =A328.43 display answer of 29.99 So no matter what the answer is it rounds up to the 9.99 answer...

Dumb question... how to create a Enterprise Formula Custom Field
Guys, I'm writing because I've tried and search but can't get it ... From PWA I created a Project Custom Field "MyField" of type Text with the formula "Hello World", I tried with " and '. When I open a project the field returns #ERROR. I published to see if magic happens, but nothing. I did the same with type number and formula 10, same result. So In the end, I can't get any formula to work. What am I doing wrong ... I googled with no success. Perhaps try opening the project and hitting the F9 button to refresh calculations? Does ...

How do I prevent Excel from auto-correcting the date format?
I am using Excel, and every time I enter 2/1 in the spreadsheet - it corrects it to Feb-1. How do I prevent it from doing this? One way is to format the input column / range as Text first (via Format > Cells > Number tab > Text > OK) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Watts" <Watts@discussions.microsoft.com> wrote in message news:16725A60-590A-4658-857A-2E6C8420D253@microsoft.com... > I am using Excel, and every time I enter 2/1 in the spreadsheet - it corrects > it to Feb-1. How do I prevent it from do...

Format Excel cells
Anyone could help me to solve this problem : How to format the selected cells so that it allows other users to "paste values" only(number from 0 to 100) to the cells. It rejects "paste" function which put formula, format etc into the cells. Thanks a million ! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreade...

Format Row Based on Cell Value Selection
Try to figure out how to write a macro button to color all rows "yellow" that match a selected cell's value in 2 ranges. Specifically, I want to be able to select a cell in column A from sheet1, click a macro button, and highlight every row in 2 ranges "Players1" and "Players2" that contains the value from the selected cell in the first column of either range. Also, wondering how to undo Thanks Hi Mike, How are the ranges named? Are they named in the interactive mode with Define Name or are they named in a macro with Set rnge = range etc? Can you pro...

search perticular result of conditional formatting
I have applied conditional format to very large range of data. Many cells are now having a perticular format as a result of these cells meeting the specified condition How can I find those cells because find-format is not doing this task. Harsh, You need to test whether the cell meets the CF conditions. For instance, if a CF condition is >6, then check which cells are >6. No automated function I am afraid. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Harsh" <harshdhandharia@hot...

Help me with formula
I have a basic graph 1,2,3,4,5.....going down in and a,b,c,d,e,f,c going across. EX. A B C D E F G ---------------------------------------------- 1 500 25 75 800 (200) 2 3 4 5 6 7 My question is how can I make an equation that Goes like this : Column A+B+D-E=G I showed an example above for on row but i wouuld wnat this to work for any row no matter what data I answer. I want this ewuation to work for any data entered for the whole sheet. I hope you understand and can help anyone. -- thatsall ------...

disable auto conversion data to date-format when pasting
I have following problem: I copy some data from webpage. the data are in format like this: 31 / 40 28 / 34 21 / 68 47 / 63 34 / 45 26 / 48 11 / 24 16 / 32 when I paste that to excel I get following: 31 / 40 28 / 34 21 / 68 47 / 63 34 / 45 26 / 48 2004-11-24 16 / 32 as I understeand excel by default tries to choose best format for dat I paste. this is really annoying because it in reality changes data paste - 11 / 24 is no longer 11 /24 - it is now 38315 <- the real wa date data is stored and I can no longer get two first and two las numbers as I would be able from 11 / ...

Lookup Formula Needed
Here is what I need the formula to do: I will have two columns, Column A will have a value and Column B will have a code. I want to Sum the numbers in Column A IF the Code in Column B has the letter "R" in it. So, using the data below, my formula would add the values 100+400+500 from column A and return a result of 1,000. Any help would be appreciated! Column A Column B 100 R1 200 M3 400 R2 500 R6 700 I1 300 M3 Try this: =3DSUMIF(B:B,"*R*",A:A) The asterisk ...

custom date format 04-12-10
I can use the built-in formats in XL2003 to get a date like " Monday, September 27, 2010". Is it possible to create a custom format to give a date like "Mon, Sept 27, 2010"? Thanks so much for all the help, past, present, and future!! Tonso How about Mon, Sep 27, 2010 If that's ok, try: ddd, mmm dd, yyyy or ddd, mmm d, yyyy Tonso wrote: > > I can use the built-in formats in XL2003 to get a date like " Monday, > September 27, 2010". Is it possible to create a custom format to give > a date like "Mon, Sept 27, 201...

formula = ADD whatever is not highlighted.
Hi. I have a workbook which I use as my monthly budget. I have tried other software made for that, but this is really what I like. As transactions go through, I "highlight" the cell, using a variety of colors. I would like 1 cell (presumably, one that would calculate, what is left to go through) to add only the cells that are not highlighted, in a certain range. Any and all help is greatly appreciated. These should help. Sub addnoncolor() mysum = 0 For Each c In [m1:m5] If c.Interior.ColorIndex = 2 Then mysum = mysum + 1 Next MsgBox mysum End Sub Sub whatcolor() For Eac...

When you insert a new Excel page, what is the Dialog format?
I added this Dialog formatted page to my worksheet, but can figure out what it does. You can use a dialog page to create an input form for your workbook. You can do it by inserting a worksheet as you have done, or you can insert a dialog box through visual basic. If you are familiar with Access forms, you will already know how to create fields on a dialog box. If not, you might want to look for help on Access forms or Excel Dialog forms. BTW, if you are using Excel Help, don't type in "dialog". Try "forms" instead. -- Cordially, Bob Sullivan Microsoft Offi...

in-cell dropdown formatting
In Excel I sometimes use data "validation" with a "list" to provide me with a drop-down list of possible entries for a cell, which can be quite useful for repetitive stuff. Unfortunately, I have not discovered how to control the font size within the drop down, and this occasionally becomes tiny and illegible. Does anybody know how to alter the drop-down text size? Typically I might have a list of 20 names. I don't think you have any control over the font size of the validation drop down list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software ...