Hi All,
I've got a spreadhseet which has 9 non-contiguous variables which need to be 'scored' and then summed. For each of A, C, E, G, I, K, M, O, Q, responses can range from 1 to 4 (although some are missing) (see attached spreadsheet). I want to be able to score each response based on the following criteria:
if answer=1 or 2, then score = 1
if asnwer=3, then score = -1
if answer=4, then score=0
if answer is missing, then score= MISSING
Using this scoring protocol, scores can range from -9 to +9. As you will see in the atached spreadsheet, some cells are missing one or two scores and other rows are missing altogether.
Here is my problem. I want to be able to score each cell (see the 'ifs' above) and then sum across the non-contiguous columns to compute a final score WITHOUT counting the blanks as zeros. In other words, how can I add or SUM items up without Excel putting a darn 'zero' in the rows where there is no data?
Hope I've explained this okay and thanks in advance for your help.
Bookmarks