Excel Conditional Adding

ComproxComprox *chortle*Canada Join Date: 2002-01-23 Member: 7Members, Super Administrators, Forum Admins, NS1 Playtester, NS2 Developer, Constellation, NS2 Playtester, Reinforced - Shadow, WC 2013 - Silver, Subnautica Developer, Subnautica Playtester, Pistachionauts
edited May 2007 in Off-Topic
Question time:

In excel, I am trying to do some addition (no, can't use anything else, at work). I would like it to go through column B (the A1, B1, etc), find all the A1's, and add the total of the number to the left of them (another column). It needs to be dynamic since the real excel file is massive and updated daily. Thoughts?
Off Topic Disagree Agree Awesome

Comments

  • FaskaliaFaskalia Wechsellichtzeichenanlage Join Date: 2004-09-12 Member: 31651Members, Constellation
    edited May 2007
    Ok this is only semi helpfull, because in the german excel version lots of functions are named differently, but excel does understand basic boolean.

    Just press F1 for the excel help and search for "boolean" the functions you are searching for should pop up. Its prolly named "IF".

    Arg, damn stupid forum limits.

    Drop me a line if you need an example file.

    OK, code from the german version:

    <!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->=WENN(B2="A5";A2;0)<!--c2--></div><!--ec2-->
    What this does, is check if b2 does contain the string A5 (hence the ""). If it does then it displays the contents of A2, otherwise it displays 0. Now all you need to do is copy that down and then add a sum at the end.

    And again the english version uses most likely IF instead of WENN.
    Off Topic Disagree Agree Awesome
  • ComproxComprox *chortle* Canada Join Date: 2002-01-23 Member: 7Members, Super Administrators, Forum Admins, NS1 Playtester, NS2 Developer, Constellation, NS2 Playtester, Reinforced - Shadow, WC 2013 - Silver, Subnautica Developer, Subnautica Playtester, Pistachionauts
    "Now all you need to do is copy that down and then add a sum at the end." That doesn't seem very automatic to me. I'd like it to find each value and add them to the toal automatically. There is literally thousands in the file so doing it by hand is way too much work.
    Off Topic Disagree Agree Awesome
  • FaskaliaFaskalia Wechsellichtzeichenanlage Join Date: 2004-09-12 Member: 31651Members, Constellation
    edited May 2007
    Ok, lets say B5 is where you entered the boolean function. Now simply select B5 through B20 and then press "ctrl+u" this will copy the formula down to the other cells, and adjust accordingly. But as I already said: If you are having trouble, drop me a line and I will mail you a sample file.

    Edit: And if you need to copy cells from left to right use "ctrl+r". You can also tell the formula to NOT adjust for line and column by adding a "$" in front of the cell-indicators.

    So "=$A$3" will always point to A3 when copied.
    "=$A3" will only adjust for the line
    "=A$3" will only adjust for the column
    Off Topic Disagree Agree Awesome
  • ComproxComprox *chortle* Canada Join Date: 2002-01-23 Member: 7Members, Super Administrators, Forum Admins, NS1 Playtester, NS2 Developer, Constellation, NS2 Playtester, Reinforced - Shadow, WC 2013 - Silver, Subnautica Developer, Subnautica Playtester, Pistachionauts
    Ok, I got it working now, silly me. Now, to be picky, this adds a ton of data all over the file, any idea if we can either clean it up or is there a more efficient way to do this?
    Off Topic Disagree Agree Awesome
  • scaryfacescaryface Join Date: 2002-11-25 Member: 9918Members
    edited May 2007
    A more efficient (cleaner) way to do this would be to use excel vba (macros)
    Off Topic Disagree Agree Awesome
  • FaskaliaFaskalia Wechsellichtzeichenanlage Join Date: 2004-09-12 Member: 31651Members, Constellation
    If you dont want to use macros and only want to get rid of the "visual" obstruction you can also:

    Mark a few cells
    right click them
    select format cells
    there select the numbers tab
    under category mark "user defined"
    write <!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->;;;<!--c2--></div><!--ec2--> where it says type

    this will hide a cells content
    Off Topic Disagree Agree Awesome
  • ComproxComprox *chortle* Canada Join Date: 2002-01-23 Member: 7Members, Super Administrators, Forum Admins, NS1 Playtester, NS2 Developer, Constellation, NS2 Playtester, Reinforced - Shadow, WC 2013 - Silver, Subnautica Developer, Subnautica Playtester, Pistachionauts
    Wow, that I did not know. Thanks for the info guys, got it looking all pretty now <img src="style_emoticons/<#EMO_DIR#>/smile-fix.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile-fix.gif" />
    Off Topic Disagree Agree Awesome
  • scaryfacescaryface Join Date: 2002-11-25 Member: 9918Members
    edited May 2007
    Since i have too much time (summer, no job yet <img src="style_emoticons/<#EMO_DIR#>/sad-fix.gif" style="vertical-align:middle" emoid=":(" border="0" alt="sad-fix.gif" /> ), i wrote the macro despite not having used excel in years and having to navigate through excel 2007 (mostly just to refresh my knowledge of excel vba, since i suck)
    (for the example file you gave)
    <!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->Sub Macro1()
        Cells(2, 2).Select
        Do While ActiveCell <> ""
            If ActiveCell.Value = "A1" Then
                Range("E3") = Range("E3") + Cells(ActiveCell.Row, ActiveCell.Column - 1)
            ElseIf ActiveCell.Value = "B1" Then
                Range("E4") = Range("E4") + Cells(ActiveCell.Row, ActiveCell.Column - 1)
            ElseIf ActiveCell.Value = "B2" Then
                Range("E5") = Range("E5") + Cells(ActiveCell.Row, ActiveCell.Column - 1)
            ElseIf ActiveCell.Value = "C3" Then
                Range("E6") = Range("E6") + Cells(ActiveCell.Row, ActiveCell.Column - 1)
            End If
            ActiveCell.Offset(1, 0).Select
        Loop
    End Sub<!--c2--></div><!--ec2-->
    but really, you're better off doing it the old fashioned way since the macro would have to be run each time for the values to be updated.

    As an alternative to what Faskalia said, to hide cells, you can also just change the font color to white like me <img src="style_emoticons/<#EMO_DIR#>/tounge.gif" style="vertical-align:middle" emoid=":p" border="0" alt="tounge.gif" />
    Off Topic Disagree Agree Awesome
  • FaskaliaFaskalia Wechsellichtzeichenanlage Join Date: 2004-09-12 Member: 31651Members, Constellation
    <!--quoteo(post=1626961:date=May 16 2007, 12:18 AM:name=scaryface)--><div class='quotetop'>QUOTE(scaryface @ May 16 2007, 12:18 AM) [snapback]1626961[/snapback]</div><div class='quotemain'><!--quotec-->
    As an alternative to what Faskalia said, to hide cells, you can also just change the font color to white like me <img src="style_emoticons/<#EMO_DIR#>/tounge.gif" style="vertical-align:middle" emoid=":p" border="0" alt="tounge.gif" />
    <!--QuoteEnd--></div><!--QuoteEEnd-->

    See, thats the reason you are the "Jester of Excel" and I am the "King of Excel". <img src="style_emoticons/<#EMO_DIR#>/tounge.gif" style="vertical-align:middle" emoid=":p" border="0" alt="tounge.gif" />
    .
    .
    .
    .
    Cheapo... CHanging the font colour to match the background. Thats sooooooo dodgy
    Off Topic Disagree Agree Awesome
  • DiscoZombieDiscoZombie Join Date: 2003-08-05 Member: 18951Members
    You should look into making a pivot table. Very good for this sort of thing. Drag Column A into the 'data' field and Column B into the 'row' field and you're done, no formulas or macros necessary.
    Off Topic Disagree Agree Awesome
  • ComproxComprox *chortle* Canada Join Date: 2002-01-23 Member: 7Members, Super Administrators, Forum Admins, NS1 Playtester, NS2 Developer, Constellation, NS2 Playtester, Reinforced - Shadow, WC 2013 - Silver, Subnautica Developer, Subnautica Playtester, Pistachionauts
    Now *that* is what I was looking for, thank you! Makes it all spiffy but the only problem I now have is how do I make it add the new data to the table (ie: every day we add a few more rows to the table).
    Off Topic Disagree Agree Awesome
  • JimmehJimmeh Join Date: 2003-08-24 Member: 20173Members, Constellation
    Why don't you use Access or SQL or something? Seems like it might be easier for what you're trying to do
    Off Topic Disagree Agree Awesome
  • ComproxComprox *chortle* Canada Join Date: 2002-01-23 Member: 7Members, Super Administrators, Forum Admins, NS1 Playtester, NS2 Developer, Constellation, NS2 Playtester, Reinforced - Shadow, WC 2013 - Silver, Subnautica Developer, Subnautica Playtester, Pistachionauts
    Yah, but Im dealing with old ladies who update it and it took them 5 years to learn excel so I am not dumping a new program on them. But, I got it all figured out finally, woooo!
    Off Topic Disagree Agree Awesome
  • DiscoZombieDiscoZombie Join Date: 2003-08-05 Member: 18951Members
    I'm not at work anymore but I *think* pivot tables are smart enough to expand the range of the source data if you add more... you just have to click the little red ! to "Refresh Pivot Table" whenever you add more data.

    But you said you figured it out already so maybe this is what you figured out, so grats <img src="style_emoticons/<#EMO_DIR#>/biggrin-fix.gif" style="vertical-align:middle" emoid=":D" border="0" alt="biggrin-fix.gif" />
    Off Topic Disagree Agree Awesome
Sign In or Register to comment.