Missing data

[Allan]

i have incomplete columns of data that i neeed to perform calculations on and generate charts. what i want is a simple way of writing a formula that will return a blank cell if any of the original source data is missing. i have two problems

1 at the moment i have to use nested if's to check each column of data and see if it contains values. this leads to long, overcomplicated formulas with lots of brackets that are hard to error check

2 to output a blank cell if the source data is missing i use "" this is fine as the cells all appear blank and are ignored for future calculations - i can get the average function to work properly with this method for instance. unfortunately charts always treat my blank looking cells as zero and the output is therefore meaningless (yes i have options / chart set to do not plot empty cells but it is not recognising them as empty). the only way i have found around it is to use NA() to output a blank cell when i have missing data. this sorts the chart out but i then can't perform calculations on my column of results as they all come back with an #NA error

how do i tell excel to output a blank cell in the event of missing data?

 

[JMay]

Highlight area to determine is blanks exist;
At the menu, Edit, Goto, Special (Button), click on BLANKS, OK
Now you see 'em. All blanks will be selected..
Not sure (from what your saying you need to do, but) If you should
want to enter say NA() - the activecell is ready so enter =NA() into it
but hold-down the Control key while pressing the Enter Key.
That will populate your entry into all the blanks... Before leaving (or
touching
the keyboard - as the same cells are still active, you may want to convert
the
formularized = NA() into #N/A....??
Good luck
HTH

i have incomplete columns of data that i neeed to perform calculations on

and generate charts. what i want is a simple way of writing a formula that
will return a blank cell if any of the original source data is missing. i
have two problems:

1 at the moment i have to use nested if's to check each column of data and

see if it contains values. this leads to long, overcomplicated formulas with
lots of brackets that are hard to error check.

2 to output a blank cell if the source data is missing i use "" this

is fine as the cells all appear blank and are ignored for future
calculations - i can get the average function to work properly with this
method for instance. unfortunately charts always treat my blank looking
cells as zero and the output is therefore meaningless (yes i have options /
chart set to do not plot empty cells but it is not recognising them as
empty). the only way i have found around it is to use NA() to output a
blank cell when i have missing data. this sorts the chart out but i then
can't perform calculations on my column of results as they all come back
with an #NA error.

 

[DEBI]

Re: Missing data Thank you JMay