Conditional data point formatting with variable conditions?

Hi,

I checked out Jon Peltier's great example/tutorial on conditional
chart formatting. I would like to conditionally format data points of
a scatter plot but the difficult thing about it is that the min-max
conditions are variable.
I have two data columns, x and y, yielding the scatter plot (Side
note: In the thread "Diagonal line in scatter plot?" I described the
data in a bit more detail.). The conditions for giving a data point a
certain color are not supposed to be fixed values but instead the
ratios of x and y. Let's say the ratio of x any y is above 1.5 or
below 0.7, then I would like the data points to be colored red instead
of black. My problem is that I can't figure out how to formulate that
condition so that it applies to each x-y pair with its own values and
not just one specific x-y pair.

Can such a condition be set up in Excel?

Peter
0
11/22/2004 10:48:09 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
679 Views

Similar Articles

[PageSpeed] 22

Peter,

The principle is exactly the same as described in Jon's site. Say yo
have your data in the Range A2:B10, with x values in A and y values i
B.

Write a formula in cell C2: = if(or(a2/b2>1.5,a2/b2<0.7),a2,na())
Write a formula in cell D2: = if(and(a2/b<=1.5,a2/b2>=0.7),a2,na())

The series in column C will represent the set of points that ar
outside the range (0.7,1.5) for x/y. 

Now you can plot columns A,C & D in a scatter plot. You can then forma
the two series C & D individually as you like.

Hope this helps,
Regards,
Leni

--
LeninVM
-----------------------------------------------------------------------
LeninVMS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=668
View this thread: http://www.excelforum.com/showthread.php?threadid=31952

0
11/22/2004 11:30:40 PM
LeninVMS wrote:
> Peter,
> 
> The principle is exactly the same as described in Jon's site. Say you
> have your data in the Range A2:B10, with x values in A and y values in
> B.
> 
> Write a formula in cell C2: = if(or(a2/b2>1.5,a2/b2<0.7),a2,na())
> Write a formula in cell D2: = if(and(a2/b2<=1.5,a2/b2>=0.7),a2,na())
> 
> The series in column C will represent the set of points that are
> outside the range (0.7,1.5) for x/y. 
> 
> Now you can plot columns A,C & D in a scatter plot. You can then format
> the two series C & D individually as you like.

That is the solution I was looking for. Thanks. I think I could have
come up with the formula for column C but not for D.
Besides, my subject line and my description were not exactly right
because the conditions are not variable, they are fixed. They are just
not based on the raw data but a transformation of those.

Peter
0
11/23/2004 9:12:14 AM
LeninVMS wrote:
> Peter,
> 
> The principle is exactly the same as described in Jon's site. Say you
> have your data in the Range A2:B10, with x values in A and y values in
> B.
> 
> Write a formula in cell C2: = if(or(a2/b2>1.5,a2/b2<0.7),a2,na())
> Write a formula in cell D2: = if(and(a2/b2<=1.5,a2/b2>=0.7),a2,na())
> 
> The series in column C will represent the set of points that are
> outside the range (0.7,1.5) for x/y. 
> 
> Now you can plot columns A,C & D in a scatter plot. You can then format
> the two series C & D individually as you like.

That is the solution I was looking for. Thanks. I think I could have
come up with the formula for column C but not for D.
Besides, my subject line and my description were not exactly right
because the conditions are not variable, they are fixed. They are just
not based on the raw data but a transformation of those.

Peter
0
11/23/2004 9:12:15 AM
Reply:

Similar Artilces:

Today Conditional Format
I have a column which calculated a date based on 3 working days from a date input into a diff column. What I want to do I have the calculated date appear bold if that date has passed based on the current date. For example if I input 01/08/11 the next column calculates 04/08/11 (3 working days). I want the caluclated date to appear bold when I open the spreadsheet on the 05/08/11 ro show the calculated date has passed. I can use conditional formatting but can't get the syntax right Regards Andy Win XP Pro Office 2010 Andy Roberts presented the following explanation : > I ha...

Can't format numbers for France in Excel 2003
I'm using the US version of Excel 2003, but need to format costs, etc., for a project in the French euro format (e.g., I need 2050.45 euros to display as 2 050,45). That currency format isn't offered in my dialog box, and if I make a custom number format that will display correctly, it doesn't operate as a number. Any suggestions? On Wed, 10 Oct 2007 08:51:03 -0700, Mary Fran <MaryFran@discussions.microsoft.com> wrote: >I'm using the US version of Excel 2003, but need to format costs, etc., for a >project in the French euro format (e.g., I need 2050.45 euros...

Two Condition Vlookup?
I am currently trying to create a function that searches through an array for two exact column values which allows me to retrieve a third column value. For example: Type of Fruit Date Packaged Amount of Fruit Apples June 100 Oranges June 50 Apples July 75 Grapes July 50 In this example, I would be looking to retrieve the Amount of Fruit (Apples) that was packaged in July. I would be putt...

Extracting selected data
Win98SE / Excel 2000 I operate a spreadsheet that tracks sales for drivers for a courier company. The workbook has multiple sheets that are all formatted in a similar way - each week of the year has 40 rows, listed one after the other on each sheet. The first sheet is a driver reference sheet listing driver names, driver numbers, do they pay for radios, rate of compensation, etc. The other sheets pull this information to compile lists of total sales, payroll information, top performers, etc. It's the top performers sheet that is causing me problems. We post a list of the top six driver...

Sending XML data to asp server... Is there any problem?
Hi, I transform client XML data to IIS server by WinInet API. In IIS server, asp parse request XML data and result save in MS-SQL. I can read XML data by this function, but I can send XML data... The function that I use is that... BOOL CXMLUtil::RequestHttpServer(LPCTSTR lpszMethod, LPCTSTR lpszURL, LPCTSTR lpszRequest, LPCTSTR lpszHeader, CString &strResponse, CString &strStatus) { BOOL fResult = TRUE; CInternetSession iSession; CHttpConnection *pHttpConnection = NULL; CHttpFile *pHttpFile = NULL; CString str_ErrorText; DWORD dwErrorCode = 0; CString strUrl = lpszURL; ...

Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...

Excel Queries using Access data
Im trying to create a query from within Excel. When I select new database query and select a database which has no securtiy login/password, no problem, works fine. However, when I try to source the data from a database which I have created using user logins/passwords it says that I do not have admin privilages to use the database even though I am putting in the correct login/username in the popup window? Please can anyone help? I've had limited experience with this sort of thing but I've always had to strip out the protection on the Access Database and create a copy before suc...

Data Migration wizard
When this tool works it is very useful but it is a very unstable and flaky product to install. Im at a client site and I have just installed it using the local sql express but when I try to start a migration I get " Data could not be retrieved from the database Try agian later" . Which data base is being referred to here? Is it the migration database on the local sql or the CRM database on the sql server that CRM uses? I can access both databases using the the sql managemnt studio. I am running rollup 7 on PC and the CRM server. Any help appreciated - Joe Are you a Syst...

variable code??
Would appreciate help with what I am doing wrong.. vba does not like the lines containing the variables for setting chart parameters: Sub last1500() Dim currentrow As Integer Dim beginrow As Integer currentrow = Worksheets("data").Range("aa1").Value beginrow = currentrow - 1500 Application.ScreenUpdating = False ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.SeriesCollection(1).Values = "=DATA! RbeginrowC5:RcurrentrowC5" ActiveChart.SeriesCollection(2).Values = "=DATA! RbeginrowC19:RcurrentrowC19" End Sub Pa...

how to save large data sets in excel
I keep getting an error stating that "not all data saved". I know that I am exceeding the 65k or so row limit, but, would like to know if there's any way around this problem From your question I presume that you are importing data to a Workshee and are achieving only the first 65,536 lines. You will need then to visit your input source and separate th remaining data from that you have managed to save. If your data is a .csv file you could try opening it it Notepad (o other similar editor), delete from the first line to the last lin already saved, then save the remaining ...

The new View Lists in GP 8.0 are too slow with larger data sets.
The view lists are very slow to pull up a large set of records when you first open the window. After the window is populated, then the performance is fine. It would be nice to design this window to pull up the records with a single SQL query instead of getting a list of records with one query and then pulling each record individually with other queries. ---------------- 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 butto...

Best way to check if a given encoding (code point) is available in the system?
Hi all, Which would be the best way to check if a given encoding (code point) is available in functions like MultiByteToWideChar and WideCharToMultiByte? I could use a test string to see if there are problems in the encoding/decoding process, but then, which string should I use which is valid in the whole set of encodings? Is it possible to pass an empty string to those functions? Then I would get a 0 value as the returned length in the conversion, but 0 is also returned when a problem occurs... Maybe this approach is the best, then call GetLastError() and check if the value returned is not ...

Need help with conditional formula (Excel 97)
Can anyone give me the correct syntax for the following formula: if A1 is blank, and B1 is more than zero, then display B1, otherwise display nothing (blank cell) Thank you JD =IF(AND(A1="",B1>0),B1,"") Success! -- met vriendelijke groetjes "Jake D" <JakeD@djhtend.com> schreef in bericht news:crkto4lv30u7uujv27ek8okr0us5t9oj10@4ax.com... > Can anyone give me the correct syntax for the following formula: > > if A1 is blank, and B1 is more than zero, then display B1, otherwise > display nothing (blank cell) > > Thank you > > ...

Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...

Format cells #7
I have Excel 2000, Win ME. Recently the pulldown command to format cells does not always respond - ie, no box appears, so I can't apply borders or control number format. The shortcut approach doesn't work either. I've tried Help..Detect and Repair but the fault remains. Any suggestions welcome. ...

Removal of thousands seperator in downloaded data
I have downloaded data with an open space as thousands seperator, an example is a number like 2 098,67. Excel will not recognize this as a number. How can I remove this thousands seperator so that the number becomes 2098,67? I need to do this to use the data in another program. See other post. -- HTH RP (remove nothere from the email address if mailing direct) "finney78" <finney78@discussions.microsoft.com> wrote in message news:CEE4BBB3-12AB-4DDD-AD0A-F88ADD242B73@microsoft.com... > I have downloaded data with an open space as thousands seperator, an example >...

How do you lock a cell with data but still allow new data entry?
I have a spreadsheet that will be accessed by multiple users. Each user will fill out a row of data, save and close the spreadsheet. We do not want users to edit rows after data is entered into a row of cells. Is there any way to lock this data to prevent it from being edited? Thanks Yes, but only with VBA and a Protected Worksheet. The code for this is available. Questions................. 1. You say users fill out a "row of data"...................how many columns would that row contain? e.g. A to F or ?? 2. Do users fill out all cells in that row's r...

vlookup multiple data
Is it possible to have a cell contain two pieces of informaton (e.g., A1 = "Me, You"), a vlookup statement to look for each pece of informaton in that cell (e.g., first "Me", then "You") in another sheet, and display the match in B1? Sheet 1 A1 = "Me, You" B1 = (after vlookup) "Art, Joe" Sheet 2 A1 = "Me" and B1 = "Art" A2 = "You" and B2 = "Joe" I tried all different kinds of =VLOOKUP with different functions, and I can't figure t out, of course I assume its possble to even use VL...

command button to move data
If it's possible, I would like to use a command button or checkbox to trigger moving data from cells in a column (b34-b41) on one worksheet to the next available ROW on another worksheet. Thanks in advance. use the macro below and assign it to any picture or text box Sub Macro1() Sheets("Sheet1").Select Range("B34:B41").Select Selection.Copy Sheets("Sheet2").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks...

Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

Data Labels Widths
Can the width of a data label for a MS Chart (in my case a pie graph) be widened. There appears to be no property for this. I have downloaded some utilities but these are for simply creating label with bespoke text and do not allow me to adjust the width... help!!! Many thanks... Mike - The maximum width of a data label seems to be proportional to the width of the chart object. You can make the chart wider to fit a longer label. Most of a chart's text elements do not allow you to control their dimensions. You can add textboxes, however, that allow you to adjust their size. The...

Pasting Chart drops data
I'm assisting a customer using Excel andPowerPoint 2002 on a Windows XP OS. Display is set to 1024x768. A chart has been created in Excel and has extra information in text boxes and manually inserted trend lines, just drawing objects layered over the chart. The drawing objects laying over the left 75% of the chart show up fine but any text boxes or lines near the right quarter of the chart just will not show up. I tried dragging them over to the middle of the chart and they show up fine. I tried changing the display settings and get pretty much the same result. The items on the c...

How to format a number as B, KB, MB, or GB?
Is there a way to format the value in a cell so that if it's it displays as <1E3 nnnB <1E6 nnnKB <1E9 nnnMB <1E12 nnnGB without changing the actual value in the cell? hi, ! > Is there a way to format the value in a cell so that > if it's it displays as > <1E3 nnnB > <1E6 nnnKB > <1E9 nnnMB > <1E12 nnnGB > without changing the actual value in the cell? I left-out the first section (nnnB), for the other three I used thi...

Unique data
I have started a workbook with each page being a month. On each page, each column being a day. Each column has identifiers (labels) being a sub-catagory by service type purchased for each unique customer. I would like to (so far, I found the <cntr> F) identify visit frequency by customer, date and service type without creating the left to right access type database. The find screen can identify month, day, with frequency, but not service type (I thought - naming the range) due to syntax error or physical impossibility of that function. If this has already been discussed, I miss...

Extracting Data #6
Hi there, Ive been given a task to extract data. And i guess this is a common task. Does anyone have any advice on this? It involves the following process.. As this is a weekly task. I need to take the previous sheet and put all the values to zero, this is usually done by copy and paste. Then make the graphs point to this page. Then make sure the date and time colun is in the correct format as some times it comes in seconds. Sort columns in ascending and descending accordingly. work out a average for one of the columns. and then in another master sheet put the min max and average value...