A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

HELP - surface plots with missing data (How?)



 
 
Thread Tools Display Modes
  #1  
Old March 9th, 2004, 06:40 PM
Takeshi
external usenet poster
 
Posts: n/a
Default 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  
Old March 9th, 2004, 11:29 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default 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  
Old March 11th, 2004, 01:15 AM
Takeshi
external usenet poster
 
Posts: n/a
Default 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  
Old March 11th, 2004, 03:13 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.