exluding missing data values from equation

[Guest]

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???

 

[T. Valko]

What kind of calculations?

Biff

 

[Guest]

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

 

[T. Valko]

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

 

[Lori]

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