topographical data maps in excel

I have seen this done, and would like to know how to create topgraphical maps 
of data sets using excel.  I want to take a sample of data ponts and plot the 
points (easy), than color the plot based upon the values of the points 
0
bobatduke (1)
11/6/2008 11:26:01 AM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
439 Views

Similar Articles

[PageSpeed] 50

You seem to have been neglected by our community. 

Yes, it can be done, if I understand your intention correctly. The 
topography in your approach means, in a sense, the 3D chart. The first two of 
the three values defininig each point specify the x- and y-coordinates (like 
on a map); the third value is represented by prescribed colors or shades. 
You must have your variables ranged in three columns. On the base of the 
first two you will create a normal scatter or line chart (trivial, as you 
mention) with suitable (full background) markers. The task then will be done 
by the following macro, while the new basic Series in the chart has been 
pre-selected: 

Option Explicit
Sub ColorPointValues()
'Petr Bezucha, 2008
Dim MarkersFormula As String, MarkersCount As Long, _
  PosDiv1 As Long, PosDiv2 As Long, ColorI As Long, _
  YRange As Range, DecisValue As Double, I As Long
With Selection
  If .ChartType <> xlXYScatter Then Exit Sub
  MarkersFormula = .Formula
  MarkersCount = .Points.Count
  PosDiv2 = InStrRev(MarkersFormula, ",")
  PosDiv1 = InStrRev(MarkersFormula, ",", PosDiv2 - 1) + 1
  Set YRange = _
    Range(Mid(MarkersFormula, PosDiv1, PosDiv2 - PosDiv1))
  For I = 1 To MarkersCount
    DecisValue = YRange(I).Offset(0, 1).Value
    '--------------------------------	
    'here you must incorporate your decision process
    'in the simplest case ColorI = DecisValue
    '--------------------------------
    .Points(I).MarkerBackgroundColorIndex = ColorI
    .Points(I).MarkerForegroundColorIndex = ColorI
  Next I
End With
End Sub

Macro uses the SERIES formula for the identification of Y-range, and its 
neighbor, the Z-range. It is taking the points (markers) one by one, looks 
into the Z-value – now DecisValue, and … the work now comes up for you: the 
gist is to obtain proper MarkerBackgroundColorIndex from the offer of the 
ColorIndex table. You can either insert one or a series of If’s, or Select 
Case, etc. All depends on your demand and the number of your color 
categories. 

You may also decide on only Y-values. The necessary downgrade in the more 
common macro is then … .Offset(0,0). … .

ColorIndex is the restriction, as for the color scale, of Excel 2003 and 
downwards. With 2007 you can enjoy unchained RBG colors. 
The details around colors you can find elsewhere. 

Respond please if successful.

Regards

-- 
Petr Bezucha


"bobatduke" wrote:

> I have seen this done, and would like to know how to create topgraphical maps 
> of data sets using excel.  I want to take a sample of data ponts and plot the 
> points (easy), than color the plot based upon the values of the points 
0
PBezucha (46)
11/11/2008 6:54:01 AM
Reply:

Similar Artilces:

Display Data by reference
I Guys, I would like to know how can I display a record from a data base by reference. I have a variable call txtFName, a database’s table call BasicData, and the field from the data base is call FirstName, In txtFname I have stored “FirstName”, my question is how can I display the data I have in FirstName using txtFname. Thanks It has to be in the forms Record Source (query or SQL statement) or use DLookup. -- Build a little, test a little. "Maracay" wrote: > I Guys, > > I would like to know how can I display a record from a data base b...

Can I display the actual data in the data field of a pivot chart?
Using Excel 2003, I am creating a pivot table using customer data similar to the following: Customer Location Interface Code 11 Word 123 12 Excel 13 Outlook 456 21 Word 22 Excel ABC 23 Outlook XYZ I am placing the Customer Location in the row area, the Interface in the column area and the Code in the data area. I would like the Code to show in the pivot table as the "123", "4...

Consolidate Data
Want to consolidate data from many flies which are in the same format to a sigle file how can i do it in the best way? You can use a DIR looping macro to open or create formulas for each workbook desired to the next available row on the destination file. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Melwin" <Melwin@discussions.microsoft.com> wrote in message news:DA906967-E126-4101-A372-76B95AB79B6A@microsoft.com... > Want to consolidate data from many flies which are in the same format to a > sigle file how can i do it...

Excel memory Error
Hello: When I go to open a new excel spreedsheet, I keep getting a Memor error. I have removed and reinstalled Office 2000. The error does no go away. I have a 80 gig hard drive with over 50 gig free. I have gig of Ram. Page file is set to 700 meg. Can anyone help me. Thank ----------------------------------------------- ~~ 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 Sal, I've had the same problem as you this ...

I'm looking for: Excel Employee Schedule Sheet...
I need it to total the daily hours & week automatically... -- Thanks for helping... Keywizz Productions, Inc. ...

Help comparing data in 2 columns and finding same data in both
Hi, I have a 2 column spreadsheet with numerical data in each column. I need to compare columns and note which numbers are in both columns (preferably in a third column) example: I have this... col A col B 1001855 1001855 1001855 1001866 1001866 1001877 1001866 1001888 1001877 1001899 I need this.... col A col B col C 1001855 1001855 X 1001855 1001866 X 1001866 1001877 X 1001866 1001888 1001877 1001899 A has over 14,000 lines and B has over 10,000 either may have duplicates. Suggestions? Thanks Max Max One way: =IF(COUNTIF(A:A,$B1)>0,"X&qu...

Analysis Cubes Sample and Live Data
I want to create analysis cubes for the Sample company as well as for the live company. It is important to have these cubes for sample data so that the users can play and get used to working with this tool. Because of the dates (2010 - 2017) in the Sample Company Fabrikam, I cannot create the cubes in the same analysis cubes Data Warehouse. Should a different data warehouse be created for the sample company? -- Patti I would install two different instances of the analysis cubes. 1-Install the analysis cubes using the default names from the installer. I think the database and An...

Setting Column Width on Imported Data
Hi all, I would like to know if it is possible to set the column width of a column I am importing to Excel from Access. I need this column for what I'm doing in the Excel file, but I don't want it so wide, or would even like it hidden. Problem is everytime the data gets updated the column goes back to really wide. Any suggestions would be greatly appreciated. Thanks, Lori. ...

Multiple copies of excel
Everytime I open a new worksheet Excel opens a new copy. As a result I cannot copy or link from one worksheet to another. I know there is a setting somewhere to correct this, I found it in Excel XP but I forgot how I did it, but I am now in Excel 2007. If you open excel, then use Office button|Open, then things work ok, right? If yes, then I'd try this: Tools|Options|General|Ignore other applications (uncheck it) (xl2003 menus) Office button|Excel Options|Advanced category|General section| uncheck Ignore other Applications (xl2007 menus) If that doesn't work, try re-registe...

Data Stroage for Windows 2008 R2 Hyper-V servers
We would like to change our backup solution from Symantec to DPM 2010. I am unclear with how much storage is required for backups of our Hyper-V servers. We currently backup our servers to disk. If I have a Windows 2008 R2 Hyper-V host with 4 vms running on that host, and each vm has 10GB of storage (40 GB total), it seems that my first backup will take approximately 40 GB. When I do my second backup, will it require an additional 40 GB of storage, or will it only store the changed files on the four VMs? How will the third backup behave? Our hosts are configured with clustere...

Data entry form #2
Hi everyone! I need to create a data entry form in excel. I need only two fields Stock no. & quantity. As soon as I enter the stock no. the curser should go to the quantit column and when I press enter it should store that data in my exce sheet ( in A1 & B1) and clear the message box for the next entry. Th next entry should be store in A2 & B2. Can someone send me the code for it. Appreciated Tom -- SMIL ----------------------------------------------------------------------- SMILE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=488 View this thr...

How do I compare data from 2 worksheets to find duplicate entries
I am a novice Excel user. I have to worksheets that have data, some of it is similar (ie. Item #'s, etc.) I want to automatically compare the reports and highlight data on one worksheet that is identicle to the data on the other worksheet. If I can't do this, I'm going to have to manually compare the data. Jack, A coded solution is probably the way to go for your challenge. You can iterate through each item on the first sheet and check to see if it exists in the second sheet before copying it to a new (report), third sheet. -- http://HelpExcel.com 1-888-INGENIO 1-888...

Data moving
I have many files. Any file has data on column X and special range. I need to a code when run it on any file, open Box similar to following: response=InputBox("Enter new row number") and after i input row number, then copy of data on column X and it's Range, paste to new row number on sheet2. Example for my first file: data on column X has Range (X2:X570),after run code, new row number in Box is 7644,then, copy of data paste to sheet2 and new Range (X7644:X8214). regards You don't say which sheet has the original data in X2:X570 so I'll pick Sheet1 ...

Excel ActiveX TextBox by VB-Coding
The intention is to construct an ActiveX Control TextBox on an Excel worksheet ; and so, the following code is generated by deploying the Control ToolBox and Macro Recorder simultaneously. Sub ActiveXTextBox() ' ' ActiveXTextBox Macro ' Macro recorded 2004-11-26 by SynthesisUserNameDotAuthor ' ' Keyboard Shortcut: Ctrl+e ' ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _ DisplayAsIcon:=False, Left:=178.2, Top:=1326.6, Width:=72, Height:=18 _ ).Select End Sub And then, the result shows that the TextBox thus added cou...

Get external data??
Hi, I have an Access 2000 program that I package with Developer. I currently only allow "Built-in toolbars" and "Menu Bar Only'. However I want the user to have the "Get external data" option under "File". What do I need to allow for the user to get this option? Thanks, -- Phil Phil wrote: > Hi, > I have an Access 2000 program that I package with Developer. I currently > only allow "Built-in toolbars" and "Menu Bar Only'. However I want the > user > to have the "Get external data" option...

Delete duplicate data.
Hi, I have thousands of rows, and each of them contains several columns of data. If I want to delete the duplicated rows, how can I do that? Example, Cust ID Name Desc 001 John sg1. 002 Sam sg2. 001 John sg1. I want to delete the duplicated customer ID of 001? Thanks. Leo Assuming that the entire row is duplicated as in you example you can use Advanced Filtering. Data > Filter > Advanced Filter > Unique Records Only. You can than copy and paste. If the entire row is not duplicated, it's a...

Replacing Excel Template
Ok, quick question... What are the potential ramifications of replacing the template file in Excel? I have created a new template file with my companys colors and want to send it to others, but don't want to mess anyone up... Thanks! You mean you want to change book.xlt in the XLStart folder? It would depend on what the other users do with excel. If they only want to make workbooks that are based on that template, then you'd be fine. But if they wanted to do other things, then I would think it wouldn't be so nice. Why not just save it as a template and have the other users...

Rename Worksheet From Cell Data
Is there a way to rename a worksheet based on a cell in the worksheet? Running the tiny macro will set the tab name to the value in B9: Sub rNmae() ActiveSheet.Name = Range("B9").Value End Sub Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from Excel: 1. ALT-F...

Excel 2003 in seperate Window
Hi, I am using multiple monitors and would like to to have a seperate workbook open on either monitor so that i can look at either monitor and see the different workbooks. At the moment the workbooks open in the same window and they cant be split. I have looked at Tools, Option, View, Show and checked and unchecked "startup Task Pane" and "windows in Taskbar" I have also tried 1. Go to Start / My Computer then right click and select explore. 2. Then Select Tools 3. Then Select Folder Options 4. Then Select File Types Tab. 5. Scroll down the list of Registered Files type...

Letter Templates from Access Data
Is it possible to create letter templates from access DB? If so how. For example I want to create an insurance letter regarding the medical necessity of bariatric surgery. I would need to be in the patients record & would like to choose a template that would place the PT name, comorbitities, etc... into a pre-written letter that would be ready to mail. -- Thanks, Andy The quick response is "Yes" you can do that. One way would be to open Access and in the Help Search field enter the phrase "mail merge". Then, take it from there. Larry "Andy" <sh...

linking data
Is it possible to have one spreadsheet (A) linked to another spreadsheet (B) so that any changes made to (A) are automatically recorded in (B)? JR The simplest way to do this, as I can see, is to use a Worksheet_Change event macro in sheet A that will copy any changes (in sheet A) to sheet B. Does this sound like what you might want? HTH Otto "JR Winder" <jwinder@daystarinc.com> wrote in message news:7D716124-023E-4356-8820-0F7F272B2A5F@microsoft.com... > Is it possible to have one spreadsheet (A) linked to another spreadsheet > (B) so that any changes made...

inserting data into password protected excel file
How do we insert data into an excel file that is password protected using ODBC connectivity. I am getting an error whenever I am tryin to insert date into the password protected excel files. Its saying cannot decrypt the file. How do I solve this issue?? ...

is excel multiuser?
can we make our worksheet multiuser? i mean not only as read only but also as writeable Murtaza, Not really multiuser. You can share a workbook (Tools - Share workbook), which allows multiple users to open and edit the workbook concurrently. When any user saves, it checks for conflicting updates (another user updated the same cell(s) and has saved the workbook). Updates aren't seen across users in real time. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "murtaza" <murtaza@murtaza.com.pk> wrote in message news...

Convert vertical data to horizontal data
I have a listing of 6 performance ratings for each employee. The ratings appear vertically. How do I convert the ratings data to a single row for each employee? Copy - paste special - transpose -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Kiley" wrote: > I have a listing of 6 performance ratings for each employee. The ratings > appear vertically. How do I convert the ratings data to a single row for > each employee? I think I need more information. I tried what you suggested but I got the following me...

Newbie Excel/Database Question
I have a database with two tables (class and book) that are joined by a link table (class_book) such that a class has many books and the same book can be used in many classes. How might I go about pulling this data into Excel and showing the classes down the left side, the books along the top, and an 'X' at the intersection of the two when a book is required by a class. TIA, Bob You can do this with a pivot table. With 2 worksheet columns "Book" an "Class" holding your data. You might have to do a "Make Table" query i Access to achieve this. Use Data...