We are required to specify why data is missing and we have therefore given it
a number value (eg 999 for missing, 777 for not applicable). However we need
to be able to exclude these values from the final calculation in order to get
the correct answer. What can we do???

What kind of calculations?

Biff

"tam25" wrote in message
...
We are required to specify why data is missing and we have therefore given
it
a number value (eg 999 for missing, 777 for not applicable). However we
need
to be able to exclude these values from the final calculation in order to
get
the correct answer. What can we do???

Mostly summing, some averaging, some reverse scoring (converting a response
from 5 to 1 or 1 to 5).


"T. Valko" wrote:

What kind of calculations?

Biff

"tam25" wrote in message
...
We are required to specify why data is missing and we have therefore given
it
a number value (eg 999 for missing, 777 for not applicable). However we
need
to be able to exclude these values from the final calculation in order to
get
the correct answer. What can we do???

How can you distinguish between a "mask value" and a real value?

As you say, you use the mask, 999, for missing. What if there is a real
value of 999, how can you tell them apart?

You'd need to make a list of all the mask values:

G1 = 999
G2 = 888
G3 = 777

Then, with this sample data:

A1 = 999
A2 = 101
A3 = 214
A4 = 888
A5 = 300

Sum excluding mask values:

=SUMPRODUCT(--(ISNA(MATCH(A1:A5,G1:G3,0))),A1:A5)

Average excluding mask values (entered as an array using the key combination
of CTRL,SHIFT,ENTER (not just ENTER)):

=AVERAGE(IF(ISNA(MATCH(A1:A5,G1:G3,0)),A1:A5))

some reverse scoring

I have no idea what you mean by that!

Biff

"tam25" wrote in message
...
Mostly summing, some averaging, some reverse scoring (converting a
response
from 5 to 1 or 1 to 5).


"T. Valko" wrote:

What kind of calculations?

Biff

"tam25" wrote in message
...
We are required to specify why data is missing and we have therefore
given
it
a number value (eg 999 for missing, 777 for not applicable). However
we
need
to be able to exclude these values from the final calculation in order
to
get
the correct answer. What can we do???

You could try entering the values you don't want to sum as text (with
a leading apostrophe e.g. '123).

On Jan 28, 5:17 am, tam25 wrote:
We are required to specify why data is missing and we have therefore given it
a number value (eg 999 for missing, 777 for not applicable). However we need
to be able to exclude these values from the final calculation in order to get
the correct answer. What can we do???