Formula to print a datum in color contingent on value.

I need a formula that will change the displayed color of a value based on 
amount.
For example: under 200=yellow, over 200=red.
Also, can I add input from a third column?
For example: under 200 and Column D=No, then yellow.
under 200 and column D=yes, then green.
0
Shadyhosta (15)
1/31/2007 4:28:02 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
395 Views

Similar Articles

[PageSpeed] 8

Try conditional formatting for the cells
Click on /scroll over cells you need to format
FORMAT>CONDITIONAL FORMATTING 


"Shadyhosta" wrote:

> I need a formula that will change the displayed color of a value based on 
> amount.
> For example: under 200=yellow, over 200=red.
> Also, can I add input from a third column?
> For example: under 200 and Column D=No, then yellow.
> under 200 and column D=yes, then green.
0
ufopilot (81)
1/31/2007 5:11:01 PM
Can I add a third variable as in my second example?  (Value greater then 200 
and Column D "yes" then red, Column D "no" then yellow?

"ufo_pilot" wrote:

> Try conditional formatting for the cells
> Click on /scroll over cells you need to format
> FORMAT>CONDITIONAL FORMATTING 
> 
> 
> "Shadyhosta" wrote:
> 
> > I need a formula that will change the displayed color of a value based on 
> > amount.
> > For example: under 200=yellow, over 200=red.
> > Also, can I add input from a third column?
> > For example: under 200 and Column D=No, then yellow.
> > under 200 and column D=yes, then green.
0
Shadyhosta (15)
1/31/2007 5:38:00 PM
Hi

Try
=AND(C2>200,D2="NO")
Format Yellow
=AND(C2>200,D2="YES")
Format Red

-- 
Regards

Roger Govier


"Shadyhosta" <Shadyhosta@discussions.microsoft.com> wrote in message 
news:5C9AC536-8399-4B0A-B080-9A3D571B2FD5@microsoft.com...
> Can I add a third variable as in my second example?  (Value greater 
> then 200
> and Column D "yes" then red, Column D "no" then yellow?
>
> "ufo_pilot" wrote:
>
>> Try conditional formatting for the cells
>> Click on /scroll over cells you need to format
>> FORMAT>CONDITIONAL FORMATTING
>>
>>
>> "Shadyhosta" wrote:
>>
>> > I need a formula that will change the displayed color of a value 
>> > based on
>> > amount.
>> > For example: under 200=yellow, over 200=red.
>> > Also, can I add input from a third column?
>> > For example: under 200 and Column D=No, then yellow.
>> > under 200 and column D=yes, then green. 


0
roger5293 (1125)
1/31/2007 6:24:33 PM
Where? Do I put these formulae in the Conditional formatting pop up box? 
Under "Cell value is"  or "formula is"?

Thanks.

"Roger Govier" wrote:

> Hi
> 
> Try
> =AND(C2>200,D2="NO")
> Format Yellow
> =AND(C2>200,D2="YES")
> Format Red
> 
> -- 
> Regards
> 
> Roger Govier
> 
> 
> "Shadyhosta" <Shadyhosta@discussions.microsoft.com> wrote in message 
> news:5C9AC536-8399-4B0A-B080-9A3D571B2FD5@microsoft.com...
> > Can I add a third variable as in my second example?  (Value greater 
> > then 200
> > and Column D "yes" then red, Column D "no" then yellow?
> >
> > "ufo_pilot" wrote:
> >
> >> Try conditional formatting for the cells
> >> Click on /scroll over cells you need to format
> >> FORMAT>CONDITIONAL FORMATTING
> >>
> >>
> >> "Shadyhosta" wrote:
> >>
> >> > I need a formula that will change the displayed color of a value 
> >> > based on
> >> > amount.
> >> > For example: under 200=yellow, over 200=red.
> >> > Also, can I add input from a third column?
> >> > For example: under 200 and Column D=No, then yellow.
> >> > under 200 and column D=yes, then green. 
> 
> 
> 
0
Shadyhosta (15)
1/31/2007 7:09:00 PM
Sorry, I thought you had already got to that part.
It is under Formula Is

-- 
Regards

Roger Govier


"Shadyhosta" <Shadyhosta@discussions.microsoft.com> wrote in message 
news:D264F2BA-AE4F-40E6-AC98-DD0D1D3D6C2B@microsoft.com...
> Where? Do I put these formulae in the Conditional formatting pop up 
> box?
> Under "Cell value is"  or "formula is"?
>
> Thanks.
>
> "Roger Govier" wrote:
>
>> Hi
>>
>> Try
>> =AND(C2>200,D2="NO")
>> Format Yellow
>> =AND(C2>200,D2="YES")
>> Format Red
>>
>> -- 
>> Regards
>>
>> Roger Govier
>>
>>
>> "Shadyhosta" <Shadyhosta@discussions.microsoft.com> wrote in message
>> news:5C9AC536-8399-4B0A-B080-9A3D571B2FD5@microsoft.com...
>> > Can I add a third variable as in my second example?  (Value greater
>> > then 200
>> > and Column D "yes" then red, Column D "no" then yellow?
>> >
>> > "ufo_pilot" wrote:
>> >
>> >> Try conditional formatting for the cells
>> >> Click on /scroll over cells you need to format
>> >> FORMAT>CONDITIONAL FORMATTING
>> >>
>> >>
>> >> "Shadyhosta" wrote:
>> >>
>> >> > I need a formula that will change the displayed color of a value
>> >> > based on
>> >> > amount.
>> >> > For example: under 200=yellow, over 200=red.
>> >> > Also, can I add input from a third column?
>> >> > For example: under 200 and Column D=No, then yellow.
>> >> > under 200 and column D=yes, then green.
>>
>>
>> 


0
roger5293 (1125)
1/31/2007 10:56:01 PM
Reply:

Similar Artilces:

pics printing in wrong place
Does anyone know why my pictures print in the wrong place? I have several pics from the same source in my publication, however, some of them print correctly and others don't. I've noticed that the ones printing incorrectly are all going to the upper left hand corner of the page. I'm very new at using Publisher and suspect that my problem may be my print driver,in which case, i'm clueless as to how to fix it. I'm using publisher 2002 and a KYOCERA-MITA 4030 printer. Hi terracotta (terracotta@discussions.microsoft.com), in the newsgroups you posted: || Does anyone kno...

Public Folder Calendar printing
Hello, I'm running Outlook 2003 in an MS Exchange 5.5 environment. One of my users posed the question of why they cannot print the daily view of a Public Folder calendar. The weekly view prints just fine but whenever, they (or even me, and I'm the owner) try to print a daily view, they get the following error message: "This folder cannot be opened because there is a configuration problem on the server. Contact your Microsoft Exchange Server administrator for assistance." I haven't been able to find anything in Outlook help, Technet or Slipstick. Anyone have any clue...

multiple colors on format of plot area
How can I change the format of the plot area to multiple colors. for example I would like to have a Bar Chart with the background plot area starting @ blue , then Green, then Red. Then have the temperature bars plot over this area. thanks You can do so by bringing a stacked column chart into the mix. Please see Jon's example below: http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html -- John Mansfield http://cellmatrix.net "BillO" wrote: > How can I change the format of the plot area to multiple colors. > > for example I would like to have a ...

can't print black
I have installed a lexmark 6570 fax wireless. It would not print any doc's in black ink prints beautiful color pictures. I uninstalled and reinstalled did not help. I figured is was the printer. Installed an HP F4280 all in one, same problem. can someone get me some advice!!! -- twilltwo ------------------------------------------------------------------------ twilltwo's Profile: http://forums.techarena.in/members/180192.htm View this thread: http://forums.techarena.in/vista-help/1297957.htm http://forums.techarena.in ...

can I get "step by step" on color seperating for commercial printi
This is kind of an extension on my previous message but I feel it would help a lot more. Can someone give me a quick step by step on making a document with text and photo into a press quality color seperated piece using PMS colors and process black? Or maybe point me to a site that can give step by step on that stuff? ...

No fill color visible...not high contrast!
Cell fill colors are not visible in Excel. This only occurs in Excel. The high resolution box is not checked. Just for fun I checked it. That would not cause an Excel problem that would cause an everything problem. It is only a problem when I am logged in. Other users do not have the problem. Any other suggestions? ...

Printing from Arranged View
Is it possible to print two workbooks, that are in a Horizontal Arranged View, at the same time and one one page? For example, workbook A (sheet1) and workbook B (sheet 1) are arranged together in a horizontal view and I like to print the contents of this view on one page. I tried the arranged feature and the compare side by side feature but did not avail. Neo, Excel prints one sheet at a time, on as many pages as it takes. It won't combine sheets. The view settings have no effect on printing. To get stuff from two sheets to print on one page, you'd have to link the d...

Fixing a formula
I have the following equation, it is looking up in another worksheet an pulling through information when it finds a match in second file fro cell d in first file. =VLOOKUP(D10,'C:\New Code Se up\[gcodenewsetupcopy.xls]Sheet1'!C4:O81,4,FALSE) This is working but when i copy the equation down a few rows th equation changes to this and it should still be looking at Sheet 1 C to O81. =VLOOKUP(D14,'C:\New Code Se up\[gcodenewsetupcopy.xls]Sheet1'!C8:O85,4,FALSE) I know i need to fix part of the equation but don't know how or whic bit. Please help -- Boethius -----------...

Replacing part of a formula
Currently I am working on a project that uses formulas to referenc another sheet in the workbook. I plan to use the newly created shee monthly. However I am in need of changing part of the cell formula. Example one cell says =Nov!D12 and I am needing to change it to rea =Dec!D12 or =Jan!D12 etc.... Any help -- Message posted from http://www.ExcelForum.com One way is to use the INDIRECT function when creating your formulas, eg:- Assuming your month was in say A3 then =INDIRECT(A3&"!A1") will give you the data from cell A1 on whatever sheet is listed in A3. Another option i...

Formula Help #23
If I have a number in cell A1 like 5 and another number in cell A3 like 6 I want the number in A7 to show -1 and if cell A3 was 4 to read +1 and if the numbers are 2 away from A1 then the numbers in cell A7 would be -2 or +2 depending on the number in cell A3. All help is appreciated. Thanks Karl You must be kidding! =A1-A3 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Karl" <Karl@discussions.microsoft.com> wrote in message news:21FB7D9E-DC79-42...

Print button on Report form
Hi all :-) =20 Access 2007 Pro Plus, Vista Ultimate SP2 I am preparing a small app for a friend who is handicapped, and am = trying to make things as simple as possible for them to use. I am trying = to limit the number of functions and clicks necessary to accomplish = tasks as much as possible, and would like to try an idea. I want to add a small button on the report form that will print the = report when clicked. I have done this on display forms to print as a = report, but, not on a report form. In trying to add such a button to the = report form in 2007 I am not being offered...

Error created when try to Print Preview or Print
When try to print or print preview system is generating an error: "Print titles must be contiguous and complete rows or columns." Used excel for years ever seen this error before. Using Excel 2007 on Vista, we just upgraded to SP2 for both systems. After testing a few times, I notice that system is generating a Name Range with no referencing cell range, the name range is called Print_Titles. If I delete the name range it removes the error message and able to use print preview and able to print. Notice it is occurring more ofter when client is copying a worksheet to ...

Options -> Security -> Zone -> Internet printing
Hi, I have a user whose Outlook 2003 settings are as follows: Options -> Security -> Zone -> Internet Any time they receive any email with graphics etc they are unable to print that email. I tried to do the same on my PC, and am also not able to print (with those settings) How do we get this to be printed? Thanks Does it work in Restricted sites zone? That's the default setting. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:...

Howto not print a background color
In my wrksht a cell range is highlighted as an indicator to various users, the highlight is a background autofill color. I would like to print with out this background color subject in the printed version. Thanks---Jeff ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements Jeff File>Page Setup>Sheet Checkmark "Black and White". Gord Dibben XL2002 On Wed, 3 ...

Flag a value in a group...
Hi all, tia for any insight. I have the following query that gives some totals for qty's in a releases table, pulling and grouping information (part number, rev and desc) from a details table. Table heirarchy is (one to manys) tblOrders -> tblOrderDetails -> tblOrderReleases SELECT tblOrderDetails.fldPart, tblOrderDetails.fldRev, tblOrderDetails.fldDescription, Min(tblOrderReleases.fldDueDate) AS cfldFirstDue, Sum([tblOrderReleases].[fldQty])-Sum([tblOrderReleases].[fldQtyToBE]) AS cfldQtyToProcess FROM tblOrders LEFT JOIN (tblOrderDetails LE...

Percentage formula in Excel spreadsheet
Hi, In my spreadsheet cell E14 adds up E2 - E14 and cell C14 adds up C2 - C14. In G14 I have put a formula to show the difference between E14 and C14 as a Percentage ( formula for G14 is =SUM(E14-C14)/ABS(E14) ). When the Formula for G14 looks at E14 and C14 all it sees is a SUM formula to add up the column. Does anyone know how to solve this problem. Many thanks for looking. Rick Not sure I understand the problem; tell us what answer or error you ar getting. Also, don't you mean you are summing C2:C*13* in C14 and E2:E*13* i E14? Oh, and you can get rid of the Sum - this ...

Can I print one page landscape and the next page portrait on same.
I am trying to print the 3 pages of one worksheet and can not figure out how to make page one print out as landscape, page 2 to print out as portrait and page 3 to print out as landscape. I'd record a macro when I printed page 1 as landscape, then page 2 as portrait and then page 3 as landscape. So I could just rerun that macro when I needed it. lisajillian wrote: > > I am trying to print the 3 pages of one worksheet and can not figure out how > to make page one print out as landscape, page 2 to print out as portrait and > page 3 to print out as landscape. -- Dave Pet...

color tabs
Hi all, any idea on how to put color to your tabs (subsheets) ? TIA DANIEL In Excel 2002, right-click on a sheet tab, and choose 'Tab Color' If you have an earlier version of Excel, the tabs can't be coloured. news.global.co.za wrote: > Hi all, any idea on how to put color to your tabs (subsheets) ? > > TIA > > DANIEL -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Thanks, yip I'll have to upgrade office97 to office xp then DAN "Debra Dalgleish" <dsd@contextures.com> wrote in message news:...

Set a default value to Drop Down Box
Is it possible to set a 'default' value to a drop down box, so that n matter what value was selected when it was saved, when the workboo opens it changes to the default value? I have a written a macro to do this for all the input cells that ar not drop downs, but cannot figure out a way to change values in dro down forms. Thanks Bria -- Message posted from http://www.ExcelForum.com What's the default value? The first on the list? If yes: Option Explicit Sub auto_open() Dim wks As Worksheet Dim myDD As DropDown For Each wks In ThisWorkbook.Worksheets ...

Using a txt string in formulae
Right this is an anoying little beggar but probably simple and im failing to see the wood from the trees so to speak ! right i have a workbook with multiple sheets and a front sheet the front sheet reports results of counts from the other sheets using =COUNT(sheet1!A:A) and =COUNTIF(Sheet1E:E,"Neg room") and other similair formula my question is this In column A i have the name of the sheet so I.E. A1=sheet1 which hyperlinks to the named sheet and columb B has the first formulae and so on and so on! my question is is there a way i can add the Word name of the sheet the Count form...

Print preview difference in MFC42 and MFC71
When my project was compiled under VC++ 6.0, when print preview is activated, the preview window is an SDI window However, under VC++.Net 2003, the print preview is now also a MDI child window, how can I change this behaviour? ...

Value pack?
Hi all, I'm looking for the Office X value pack with the german/french dictionaries and so on, I seem to have deleted mine by mistake. Could someone maybe mail it to me? Thanks :) In article <BB81381D.6387%n.prisi@b3design.ch>, "? Nicholas.Prisi ?" <n.prisi@b3design.ch> wrote: > Hi all, > > I'm looking for the Office X value pack with the german/french dictionaries > and so on, I seem to have deleted mine by mistake. Could someone maybe mail > it to me? > The Value Pack is a folder on your install CD. Yeah, unfortunatly I lost my cd whil...

Error when printing account numbers
Using Dyamics 9, whenever we try to print something out of Dynamics the account numbers don't print correctly. The numbers aren't what they should be and it prints squares instead of some numbers. For example, instead of printing 10-13400-000-0000 it prints 10-400X0- X0- (with squares in place of X's). This occurs anywhere we try to print within Dynamics, but doesn't occur when we use Frx to create reports. Additionally not every computer has this problem, but we can't find any difference between the computers that would cause this. Check the fonts assigned on ...

display value axis on both sides
I'm trying to display the y axis on both the left and right side of the chart; is this possible, and if so, how to i go about it? thanks. This requires having a primary and secondary axis. The minimum requirement here is to have two series. The best way to manage this is to add a range to the worksheet using, say, the first two X values for X, and the minimum and maximum values in all of your Y data for Y. Add this range to your chart as a new series. If your main chart is a column chart, change this series only to a line chart type (Chart menu > Chart Type); otherwise use the s...

Formula Help #31
I have 12 columns in a spreadsheet. One for every month of the year. As each month is completed the data for that month goes in that column. I need a fromula in a specific cell that will return the value of the most recent column (month) that is filled in. So in July it returns the data for July but will return the data for August when a value is placed in that cell. Any suggestions? Thank you. Steve Hubbard Steve, To return the right-most value from row 2 (Assuming your data start starts in column A and extends to the right) use the array formula (entered with Ctrl-Shift-Enter): =IND...