+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 : Assigning values in cells and then summing total score excluding blanks

  1. #1
    Registered User
    Join Date
    08-29-2010
    Location
    Athens
    MS-Off Ver
    Excel 2007
    Posts
    10

    Assigning values in cells and then summing total score excluding blanks

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: HELP Assigning values in cells and then summing total score excluding blanks

    Welcome to the forum.

    In A25, =SUMPRODUCT((A1:A23={1,2,3,4}) * {1,1,-1,0})
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    08-29-2010
    Location
    Athens
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Assigning values in cells and then summing total score excluding blanks

    Quote Originally Posted by shg View Post
    Welcome to the forum.

    In A25, =SUMPRODUCT((A1:A23={1,2,3,4}) * {1,1,-1,0})
    hi, sorry should have been clearer, the scoring is supposed to take place across rows not columns, so the A1:A23 will not work, I need something which assigns and then adds up A1, A3, A5, etc.

  4. #4
    Registered User
    Join Date
    08-29-2010
    Location
    Athens
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Assigning values in cells and then summing total score excluding blanks

    i tried the following to no avail, any ideas? is what i am trying to do possible??

    =SUMPRODUCT((D2,G2,J2,M2,P2,S2,V2,Y2,AB2={1,2,3,4}) * {1,1,-1,0})

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: Assigning values in cells and then summing total score excluding blanks

    In S1: =SUMPRODUCT((A1:Q1={1;2;3;4}) * {1;1;-1;0})

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Assigning values in cells and then summing total score excluding blanks

    I've just realised what this means, after ages spent going down the wrong road!
    .....without Excel putting a darn 'zero' in the rows where there is no data
    Original posted content removed.

    Using shgs' Formula in S1
    Please Login or Register  to view this content.

    And in R1 (to count missing entries)
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Marcol; 08-30-2010 at 02:47 AM. Reason: Misread the question

  7. #7
    Registered User
    Join Date
    08-29-2010
    Location
    Athens
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Assigning values in cells and then summing total score excluding blanks

    Quote Originally Posted by shg View Post
    In S1: =SUMPRODUCT((A1:Q1={1;2;3;4}) * {1;1;-1;0})
    Hi shg,
    Thanks again fro your prompy and help ful reply. The cells in B1, E1, G1 etc. have values in them so putting in the range A1:Q1 will score these cells as well. Can SUMPRODCUT be used with cells which are not next to each other, ie. non-contiguous? It would saveme having to copy the workseet to a new one and deleting out the columns of data which are causing the problems.

    Cheers.

  8. #8
    Registered User
    Join Date
    08-29-2010
    Location
    Athens
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Assigning values in cells and then summing total score excluding blanks

    Quote Originally Posted by Marcol View Post
    I've just realised what this means, after ages spent going down the wrong road!


    Original posted content removed.

    Using shgs' Formula in S1
    Please Login or Register  to view this content.

    And in R1 (to count missing entries)
    Please Login or Register  to view this content.
    Hi Marcol,
    Thanks to you ahd shg for very helpful replies. We are getting close but the columns in between a1 and c1 have data in them but are not supposed to be part of the scoring formula- here is a new spreadsheet which gives a fuller picture. As you will see the range in SUMPRODUCT will pick up on these and not give the correct score at the end of the row. What I did was copy the worksheet into a new one and remove the columns which are causing problems and the revised formula both you and shg sent are great, but I was wondering whether this was necessary. In other words, can SUMPRODUCT be used with non-contigious cells? b2, d2, f2, etc. while leaving out c2, e2, g2, etc.?? [please see spreadsh\eet for futher info]

    I really do appreciate the time you and shg have taken to answer my query- this problem has racked my head for the last day or so.

    Cheers
    Attached Files Attached Files

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Assigning values in cells and then summing total score excluding blanks

    I can't see an easy formula for this problem, so I have written a couple of UDFs. (I can hear the boos and hisses already)....
    Please Login or Register  to view this content.
    Entered thus
    Please Login or Register  to view this content.

    The Incremental step is optional the default is 2, if you need a different step then enter the function so
    Please Login or Register  to view this content.

    Hope this helps.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  10. #10
    Registered User
    Join Date
    08-29-2010
    Location
    Athens
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Assigning values in cells and then summing total score excluding blanks

    Marcol,
    THANKS EVER SO MUCH! I can only imagine how long it too you to do this, and it is much appreciated. I will have a go at gettin this to work. All the very best!!

    Quote Originally Posted by Marcol View Post
    I can't see an easy formula for this problem, so I have written a couple of UDFs. (I can hear the boos and hisses already)....
    Please Login or Register  to view this content.
    Entered thus
    Please Login or Register  to view this content.

    The Incremental step is optional the default is 2, if you need a different step then enter the function so
    Please Login or Register  to view this content.

    Hope this helps.

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: Assigning values in cells and then summing total score excluding blanks

    PLEASE don't quote whole posts. Use the REPLY button, not the QUOTE button.

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1