+ Reply to Thread
Results 1 to 12 of 12

How to sum values depending on other columns (related variables) - please see example.

  1. #1
    Registered User
    Join Date
    05-21-2012
    Location
    Earth
    MS-Off Ver
    linux + python + numpy + pandas
    Posts
    18

    How to sum values depending on other columns (related variables) - please see example.

    Hello,
    I find this question difficult to describe in words, so I hope this will do the trick:
    I have binary data in four columns: A, B, C and D. These are arranged in rows (a large number). In a fifth column, S, I want to Sum all the 1's in a given column (A, B, C or D), thereby obtaining a "column height" for each set of 1's.
    The output would look something like this:

    A B C D S
    0 1 0 0 1
    1 0 0 0 2
    1 0 0 0 2
    0 0 1 0 1
    0 1 0 0 1
    1 0 0 0 1
    0 1 0 0 1
    1 0 0 0 3
    1 0 0 0 3
    1 0 0 0 3
    0 1 0 0 1
    0 0 1 0 4
    0 0 1 0 4
    0 0 1 0 4
    0 0 1 0 4

    You can hopefully see better from the example than my explanation what I am trying to achieve. Columns A, B, C and D are the input, and Column S is what I am trying - and currently failing - to calculate.

    Any help gratefully appreciated !

    Thank you for taking the time to read this, whether or not you can help.

    Rob

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How to sum values depending on other columns (related variables) - please see example.

    As there are only 0's and 1's, can you simply (in E2 for example, dragged down) put

    =SUM(A2:D2)
    ?
    EDIT: Okay, looking at your sums, you are obviously doing something else. Cant' see it being a power of 2 thing. Exactly how are you summing?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How to sum values depending on other columns (related variables) - please see example.

    You say you want to Sum the columns but there are 4 columns and only 1 Summation value?

    Okay, I see it now. Putting on my thinking cap. Will there always only be one 1 in any particular row?

  4. #4
    Registered User
    Join Date
    05-21-2012
    Location
    Earth
    MS-Off Ver
    linux + python + numpy + pandas
    Posts
    18

    Re: How to sum values depending on other columns (related variables) - please see example.

    Quote Originally Posted by ChemistB View Post
    As there are only 0's and 1's, can you simply (in E2 for example, dragged down) put

    =SUM(A2:D2)
    ?
    EDIT: Okay, looking at your sums, you are obviously doing something else. Cant' see it being a power of 2 thing. Exactly how are you summing?
    Hi, first thank you for your time.
    well, I don't think that works, if I '=sum(A2:D2) , it returns 1 for each line, whereas for example on line 2, I want a 2 returned, and on line 13, I want a 4 returned.
    do you see what I'm trying to achieve?

    Rob

  5. #5
    Registered User
    Join Date
    05-21-2012
    Location
    Earth
    MS-Off Ver
    linux + python + numpy + pandas
    Posts
    18

    Re: How to sum values depending on other columns (related variables) - please see example.

    Quote Originally Posted by ChemistB View Post
    You say you want to Sum the columns but there are 4 columns and only 1 Summation value?

    Okay, I see it now. Putting on my thinking cap. Will there always only be one 1 in any particular row?
    :-) !!
    thank you ! my thinking cap is dry now.. so any help more than welcome !

  6. #6
    Registered User
    Join Date
    05-22-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to sum values depending on other columns (related variables) - please see example.

    Hi Rob,
    I'm not quite sure what you are trying to do.
    What is the difference between the second and the sixth row concerning the calculations for the S column ?
    JiMcOt

  7. #7
    Registered User
    Join Date
    05-21-2012
    Location
    Earth
    MS-Off Ver
    linux + python + numpy + pandas
    Posts
    18

    Re: How to sum values depending on other columns (related variables) - please see example.

    Quote Originally Posted by jimcot View Post
    Hi Rob,
    I'm not quite sure what you are trying to do.
    What is the difference between the second and the sixth row concerning the calculations for the S column ?
    Hi,

    basically I'm trying to get a column that tells me how many consecutive 1's there are in either A, B, C or D.
    is that any help? I realise my question is perhaps not worded as well as it should be..

    Rob

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How to sum values depending on other columns (related variables) - please see example.

    Okay, Assuming your data is in A2:D16
    First, put a string of 0's in the row under your last row (i.e A17=0,B17=0,C17=0 and D17=0)
    In E2

    =MATCH(0,INDEX(A2:D$17,,MATCH(1,A2:D2,0)),0)-1

    In E3

    =IF(MATCH(1,A3:D3,0)=MATCH(1,A2:D2,0),E2,(MATCH(0,INDEX(A3:D$17,,MATCH(1,A3:D3,0)),0)-1))
    Does that work for you?

  9. #9
    Registered User
    Join Date
    05-21-2012
    Location
    Earth
    MS-Off Ver
    linux + python + numpy + pandas
    Posts
    18

    Re: How to sum values depending on other columns (related variables) - please see example.

    Quote Originally Posted by ChemistB View Post
    Okay, Assuming your data is in A2:D16
    First, put a string of 0's in the row under your last row (i.e A17=0,B17=0,C17=0 and D17=0)
    In E2

    =MATCH(0,INDEX(A2:D$17,,MATCH(1,A2:D2,0)),0)-1

    In E3

    =IF(MATCH(1,A3:D3,0)=MATCH(1,A2:D2,0),E2,(MATCH(0,INDEX(A3:D$17,,MATCH(1,A3:D3,0)),0)-1))
    Does that work for you?
    Hi,

    well I've tried that but doesn't quite do it, here is the sheet output:
    A B C D Excel1 Excel2 S
    0 1 0 0 1 2 1
    1 0 0 0 2 2 2
    1 0 0 0 1 1 2
    0 0 1 0 1 1 1
    0 1 0 0 1 1 1
    1 0 0 0 1 1 1
    0 1 0 0 1 3 1
    1 0 0 0 3 3 3
    1 0 0 0 2 2 3
    1 0 0 0 1 1 3
    0 1 0 0 1 4 1
    0 0 1 0 4 4 4
    0 0 1 0 3 3 4
    0 0 1 0 2 2 4
    0 0 1 0 1 #N/A 4
    0 0 0 0 #N/A #N/A

    The last line is that string of 0's, and column G is the desired output.
    You're way closer than I was though..

    Rob

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How to sum values depending on other columns (related variables) - please see example.

    Are columns E and F relevant? If so, what are they? See my attachment. Don't drag the formula down into the row of zeros.

    Edit: No need to quote my posts back, it just clutters up the board. Thanks
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-21-2012
    Location
    Earth
    MS-Off Ver
    linux + python + numpy + pandas
    Posts
    18

    Re: How to sum values depending on other columns (related variables) - please see example.

    Columns E and F were just the columns including your formulae, and noted, I won't quote.. new user here so thanks for the hint !
    Ok, I've opened your spreadsheet and I think I just dragged/copied the wrong way. Your sheet is exactly what I'm trying to get. I'll give it a try, and if aok post Solved.
    thanks again !!!

    Rob

  12. #12
    Registered User
    Join Date
    05-21-2012
    Location
    Earth
    MS-Off Ver
    linux + python + numpy + pandas
    Posts
    18

    Re: How to sum values depending on other columns (related variables) - please see example.

    Perfect !

    many thanks, and I'll post thread solved, thanks again !

    Rob

+ 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