If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
HELP - surface plots with missing data (How?)
Hi All,
I have a table of data (height -x, age -y, weight - z). The data is placed into a table by a macro as ff: Rows = Height, Columns = Age, and the cell value is the average of all samples having the same age and height. However, some of these cells are empty. When I construct a 3D surface plot from the data, I get nasty spikes as a result of the missing data, (it appears a zero is plotted in place of missing values). I have tried everything I can think of - changing the range (and axis) of the individual series for e.g., but no dice... The table looks something like this: 5 10 15 20 1 2 x 4 7.5 2 x 6 9 x 3 5 7 10 x Where x is a missing value (i.e. empty cell). Any help/suggestions will be much appreciated. MTIA |
#2
|
|||
|
|||
HELP - surface plots with missing data (How?)
Takeshi -
You're stuck. Surface charts treat blanks, text, error values as zeroes in the data (so do Area charts, but most other types give the option of skipping blanks, and most just draw a line over the special #N/A error. Could you make your macro insert a value into the otherwise blank cells? Something like the average of the surrounding cells might be sufficient. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Takeshi wrote: Hi All, I have a table of data (height -x, age -y, weight - z). The data is placed into a table by a macro as ff: Rows = Height, Columns = Age, and the cell value is the average of all samples having the same age and height. However, some of these cells are empty. When I construct a 3D surface plot from the data, I get nasty spikes as a result of the missing data, (it appears a zero is plotted in place of missing values). I have tried everything I can think of - changing the range (and axis) of the individual series for e.g., but no dice... The table looks something like this: 5 10 15 20 1 2 x 4 7.5 2 x 6 9 x 3 5 7 10 x Where x is a missing value (i.e. empty cell). Any help/suggestions will be much appreciated. MTIA |
#3
|
|||
|
|||
HELP - surface plots with missing data (How?)
Thanks Jon, atleast I know it's not me just being dumb ! ;-)
Jon Peltier wrote: Takeshi - You're stuck. Surface charts treat blanks, text, error values as zeroes in the data (so do Area charts, but most other types give the option of skipping blanks, and most just draw a line over the special #N/A error. Could you make your macro insert a value into the otherwise blank cells? Something like the average of the surrounding cells might be sufficient. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Takeshi wrote: Hi All, I have a table of data (height -x, age -y, weight - z). The data is placed into a table by a macro as ff: Rows = Height, Columns = Age, and the cell value is the average of all samples having the same age and height. However, some of these cells are empty. When I construct a 3D surface plot from the data, I get nasty spikes as a result of the missing data, (it appears a zero is plotted in place of missing values). I have tried everything I can think of - changing the range (and axis) of the individual series for e.g., but no dice... The table looks something like this: 5 10 15 20 1 2 x 4 7.5 2 x 6 9 x 3 5 7 10 x Where x is a missing value (i.e. empty cell). Any help/suggestions will be much appreciated. MTIA |
#4
|
|||
|
|||
HELP - surface plots with missing data (How?)
Sometimes Excel makes all of us feel dumb!
- Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Takeshi wrote: Thanks Jon, atleast I know it's not me just being dumb ! ;-) Jon Peltier wrote: Takeshi - You're stuck. Surface charts treat blanks, text, error values as zeroes in the data (so do Area charts, but most other types give the option of skipping blanks, and most just draw a line over the special #N/A error. Could you make your macro insert a value into the otherwise blank cells? Something like the average of the surrounding cells might be sufficient. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Takeshi wrote: Hi All, I have a table of data (height -x, age -y, weight - z). The data is placed into a table by a macro as ff: Rows = Height, Columns = Age, and the cell value is the average of all samples having the same age and height. However, some of these cells are empty. When I construct a 3D surface plot from the data, I get nasty spikes as a result of the missing data, (it appears a zero is plotted in place of missing values). I have tried everything I can think of - changing the range (and axis) of the individual series for e.g., but no dice... The table looks something like this: 5 10 15 20 1 2 x 4 7.5 2 x 6 9 x 3 5 7 10 x Where x is a missing value (i.e. empty cell). Any help/suggestions will be much appreciated. MTIA |
Thread Tools | |
Display Modes | |
|
|