Solved

Need to convert 3 columns of data to table to make surface chart

Posted on 2016-09-22
8
32 Views
Last Modified: 2016-09-22
Hello experts, I have a large file of raw data... basically 3 columns of data - an X and a Y inputs, and a Z output... looking like this:

20	20	6
20	50	5.99
20	80	6.02
20	110	6.07
20	140	6.12
20	170	6.26
20	200	6.31
20	230	6.34
20	260	6.37
20	290	6.41
50	20	4.1
50	50	4.08
50	80	4.07
50	110	4.1
50	140	4.16
50	170	4.22
50	200	4.22
50	230	4.22
50	260	4.22
50	290	4.24
80	20	5.77
80	50	5.75
80	80	5.74
80	110	5.76
80	140	5.83
80	170	5.88
80	200	5.92
80	230	5.92
80	260	5.94
80	290	5.95
110	20	5.81

Open in new window


The 1st and 2nd columns of data both vary from 20 to 290 in increments of 30. The 3rd value is the corresponding "Z" value. My goal is to make a 3-D surface chart of these values.  Not knowing anything about Excel Pivottables, I started fooling with that and managed to get *very* close to what I want - see attachment "ExcelSheet.jpg". Only thing slightly messing up my nice chart is the fact that it blanks out the value where I have circled in red. As you can see from my raw data, that value should be 6. I can't figure out a way to "manually" put that value back in the table. Then my chart would be perfect.
   Also, crudely pointed at in green at the right of my screenshot, it notes one of the fields is "202". Where did it get that from? Lastly, circled in blue at the bottom right, it shows under "Values", "Sum of 6". I suspect this is why my one piece of data is missing in my final table... but I just don't understand why it did these several "weird" things.

Thanks!
   Shawn
ExcelSheet.jpg
0
Comment
Question by:shawn857
  • 4
  • 4
8 Comments
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41811782
looking at the pivot table in your image, I see "Sum of 6" indicating that it is using the first row as labels, not data. Try adding a row above the data with labels to include in the pivot table range
0
 

Author Comment

by:shawn857
ID: 41811798
That was exactly it, thank you! I notice there is a thing called a "PivotChart" too - would this save me a step?

Thanks!
   Shawn
0
 
LVL 19
ID: 41811801
you're welcome

> "PivotChart... would this save me a step?"
without knowing more about your process, all I can suggest is to try it ...
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:shawn857
ID: 41811804
Well, I simply want to make the 3d surface chart from my columns of data. Would the PivotChart do everything all at once - make the table AND chart? I'll give it a try....

Thanks
   Shawn
0
 
LVL 19
ID: 41811807
you're welcome, Shawn -- please let us know
0
 

Author Comment

by:shawn857
ID: 41811817
Yeah, doesn't seem to do what I want, I will stick with the PivotTable, then making the chart from that... excellent!

Thanks
   Shawn
0
 

Author Closing Comment

by:shawn857
ID: 41811818
Thanks!
0
 
LVL 19
ID: 41811820
you're welcome, Shawn ~ happy to help
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question