performing calculations depending on the values in cells

[Loop-junkie]

Hi Everyone,
I am trying to get SPSS to perform calculation on values in a column based on values in another cell.
Something like this:

IF T1_Total_Missing_Items = 2
COMPUTE T1_ALL_Items_Total/32
Else IF T1_Total_Missing_Items = 3
COMPUTE T1_ALL_Items_Total/31
ELSE (T1_ALL_Items_Total)/34
END IF.

I know this code is incorrect but I'm just trying to convey the idea. I this even possible in SPSS?

Apologies for what even seems to be to be an annoying post!

[Bruce Weaver]

It looks like you have 34 items for something called T1 (Test 1?), and want to compute a T1-mean.  But your problem is that some people are missing 2 or 3 of the items.  If that is correct, you could just compute the mean directly using the MEAN function.  E.g., supposing your 34 items are named T1.1, T1.2, ...T1.34, and that they are contiguous in the data file:

COMPUTE T1mean = MEAN(T1.1 to T1.34).

This will compute a mean using all valid values of the variables T1.1 to T1.34.  (Replace my variable names with your own; and if your variables are not contiguous in the file, in stead of using the keyword TO, insert a comma-separated list of the 34 variables.)  

It doesn't appear to be a concern in your current dataset, but suppose there were some folks who had only a very small number of valid values, and you wished to compute a mean only for people who had at least some minimum number of valid values.  E.g., suppose you wanted to ensure at least 20 valid values.  The MEAN function can be used to do that, as follows:

COMPUTE T1mean = MEAN.20(T1.1 to T1.34).

Finally, if you really do need the sum of the valid values for some reason (your variable T1_ALL_Items_Total), note that there is a SUM function that also allows things like:

COMPUTE T1_ALL_Items_Total = SUM(T1.1 to T1.34).

or...

COMPUTE T1_ALL_Items_Total = SUM.20(T1.1 to T1.34).

HTH.

Loop-junkie wrote

Hi Everyone,
I am trying to get SPSS to perform calculation on values in a column based on values in another cell.
Something like this:

IF T1_Total_Missing_Items = 2
COMPUTE T1_ALL_Items_Total/32
Else IF T1_Total_Missing_Items = 3
COMPUTE T1_ALL_Items_Total/31
ELSE (T1_ALL_Items_Total)/34
END IF.

I know this code is incorrect but I'm just trying to convey the idea. I this even possible in SPSS?

Apologies for what even seems to be to be an annoying post!

[Art Kendall]

If I understand your example syntax you are trying to find the mean of 34 items.

If that is so see if syntax something like this will work.  This assumes that you want at least 20 of the items to be valid in order to compute a score.
compute mean_score = mean.20(item01 to item34).

depending on the specifics of your situation you should differentiate missing values that are missing reasons,
compute valid_items= nvalid(item01 to item34).
if valid_items eq 0 mean_score = -1.
if range(valid_items,1,19) mean_score = -2.
missing values mean_score (-1, -2).
value labels mean_score
 -1 'all items missing'
 -2 'some items valid but not enough'.

[David Marso]

Aside from the approach being incredibly clumsy (see the MEAN function as Art suggested).
For future reference perhaps your syntax should read... You appear to have left out cases where 1 item is missing or more than 3?

DO IF T1_Total_Missing_Items EQ 2 .
COMPUTE SomeNewVariable= T1_ALL_Items_Total/32.
Else IF T1_Total_Missing_Items EQ 3 .
COMPUTE SomeNewVariable= T1_ALL_Items_Total/31.
ELSE .
COMPUTE SomeNewVariable= (T1_ALL_Items_Total)/34
END IF.

Again, consult the docs for the MEAN function ( in particular the MEAN.n variant).
Note, you could use = in the comparator but many of us choose to use EQ so as not to conflate assignment with comparison.
HTH

Loop-junkie wrote

Hi Everyone,
I am trying to get SPSS to perform calculation on values in a column based on values in another cell.
Something like this:

IF T1_Total_Missing_Items = 2
COMPUTE T1_ALL_Items_Total/32
Else IF T1_Total_Missing_Items = 3
COMPUTE T1_ALL_Items_Total/31
ELSE (T1_ALL_Items_Total)/34
END IF.

I know this code is incorrect but I'm just trying to convey the idea. I this even possible in SPSS?

Apologies for what even seems to be to be an annoying post!

[Loop-junkie]

Hi Guys,

Thanks for the input.

"Aside from the approach being incredibly clumsy..." Yes I am very aware of that  but I'm not anything close to thinking like a programmer so I'm trying to muddle through!

David, from your reply I think you seem to have grasped most fully what I'm trying to achieve, but probably didn't explain very well.

If my column T1_Total_Missing_Items contains a value of zero I need to calculate my mean based on a divider of 34 for those cases missing zero items.
If my column T1_Total_Missing_Items contains a value of 1 I need to calculate my mean based on a divider of 33 for those cases missing 1 item
If my column T1_Total_Missing_Items contains a value of 2 I need to calculate my mean based on a divider of 32 for those cases missing 2 items
and so on down to a maximum of 4 missing items which requires a divider of 30 in order to calculate my mean for those cases missing 4 items

David's Do...IF appears to do this.
I'll give it a try and see.

I guessing that after I create the new variables I am going to have to merge them into a single column again before I continue with further analysis.

Thanks to all of you and I'll let you know how it goes.,
Best regards,
Charles.

[David Marso]

Charlie,
PLEASE look at the MEAN function.  It does precisely what that clumsy code would do!
Considering that 3 different experienced people suggested it might imply that it is the correct solution?
D

Loop-junkie wrote

Hi Guys,

Thanks for the input.

"Aside from the approach being incredibly clumsy..." Yes I am very aware of that  but I'm not anything close to thinking like a programmer so I'm trying to muddle through!

David, from your reply I think you seem to have grasped most fully what I'm trying to achieve, but probably didn't explain very well.

If my column T1_Total_Missing_Items contains a value of zero I need to calculate my mean based on a divider of 34 for those cases missing zero items.
If my column T1_Total_Missing_Items contains a value of 1 I need to calculate my mean based on a divider of 33 for those cases missing 1 item
If my column T1_Total_Missing_Items contains a value of 2 I need to calculate my mean based on a divider of 32 for those cases missing 2 items
and so on down to a maximum of 4 missing items which requires a divider of 30 in order to calculate my mean for those cases missing 4 items

David's Do...IF appears to do this.
I'll give it a try and see.

I guessing that after I create the new variables I am going to have to merge them into a single column again before I continue with further analysis.

Thanks to all of you and I'll let you know how it goes.,
Best regards,
Charles.

[Loop-junkie]

Hi Guys,

As I read your responses and looked at the MEAN function in the documentation I'm getting a little clearer on why the solution isn't sitting quite right with me (maybe erroneously).

I don't have responses to each of the items from the questionnaires. I am being supplied with a total score and a value for the number of items missing from the overall number of questions, i.e. 0,1,2,3 or 4.
This is why I'm thinking that the MEAN.n function isn't a solution for me because I can't define what items are missing for SPSS to exclude them from the MEAN calculation.

To reiterate:
I have one column with Total scores
I have another column holding a value for the number of Missing Items
If the Missing Items value is 0 I need SPSS to calculate the MEAN score for that case by dividing the Total Score by 34
If the Missing Items value is 1 I need SPSS to calculate the MEAN score for that case by dividing the Total Score by 33 and so on

Does that make sense?

[David Marso]

Nice to spell these things out from the beginning ;-)

COMPUTE avg=Total/(34-nmissing).

Loop-junkie wrote

Hi Guys,

As I read your responses and looked at the MEAN function in the documentation I'm getting a little clearer on why the solution isn't sitting quite right with me (maybe erroneously).

I don't have responses to each of the items from the questionnaires. I am being supplied with a total score and a value for the number of items missing from the overall number of questions, i.e. 0,1,2,3 or 4.
This is why I'm thinking that the MEAN.n function isn't a solution for me because I can't define what items are missing for SPSS to exclude them from the MEAN calculation.

To reiterate:
I have one column with Total scores
I have another column holding a value for the number of Missing Items
If the Missing Items value is 0 I need SPSS to calculate the MEAN score for that case by dividing the Total Score by 34
If the Missing Items value is 1 I need SPSS to calculate the MEAN score for that case by dividing the Total Score by 33 and so on

Does that make sense?

[Loop-junkie]

Hi David,

Thanks for the solution advice. It works perfectly.
Interacting with you and the rest of the guys has certainly sharpened up my thinking in terms of how to approach SPSS code.

Many thanks and I'll contribute to the forum in any way I can in the future.
Best regards,
Charles.