Excel Help

Kido
Kido Posts: 473 Forumite
in Techie Stuff
I'm trying to work out a formula or Macro or something to split up a large excel spreadsheet containing about 5000 entries.

Basically it has 6 columns, the first is the name, the second is the amount for one type, the third, fourth and fifth are an amounts for other types and the last one is the total of the previous 4 columns.

What I'd like is to be able to (if possible) take all the figures from the final column which are e.g. 1s and add them together with all the -1s from the same column. This I'd then like to be repeated against all the numbers (e.g. 2s and -2s added together) and where the totals on specific amounts don't equal zero I'd like to be able to see easily.

At the moment I'm having to do all this manually as my predecessor at work was terrified of playing with Excel where as I want to experiment and see what it can do.

I hope someone can point me in the right direction.

Thanks.
«1

Comments

  • stragglebod
    stragglebod Posts: 1,324 Forumite
    First Post Name Dropper First Anniversary
    edited 14 October 2019 at 8:02PM
    Use a pivot table with the Count function - it'll give you separate counts for the negatives and positives but I'm sure there's a way to automate that too.

    Just thought about this - it's not clear if you actually want to add or count - e.g. should 2,-2,2 result in 3 or 6?

    You can automate merging the positives and negatives by adding an extra column to calculate the absolute value of the 6th using ABS
    https://www.ablebits.com/office-addins-blog/2018/07/04/excel-absolute-value-abs-function/

    Then run the pivot table against that column.
  • stragglebod
    stragglebod Posts: 1,324 Forumite
    First Post Name Dropper First Anniversary
    Further update - to avoid adding a 7th colum you can use a calculated field in the pivot table, though I don't like to use those as it adds complexity to the table.


    If you actually want to use the results in some other caluclation you can use the getpivotdata function to pull the results into e.g. another cell.
  • Kido
    Kido Posts: 473 Forumite
    stragglebod wrote: »
    Use a pivot table with the Count function - it'll give you separate counts for the negatives and positives but I'm sure there's a way to automate that too.

    Just thought about this - it's not clear if you actually want to add or count - e.g. should 2,-2,2 result in 3 or 6?

    You can automate merging the positives and negatives by adding an extra column to calculate the absolute value of the 6th using ABS
    https://www.ablebits.com/office-addins-blog/2018/07/04/excel-absolute-value-abs-function/

    Then run the pivot table against that column.

    The result should be all the 2s minus all the -2s. So if both columns B&C (or D&E) have two number 2s in them I'd like the formula or pivot table to be able to extract all the 2s and minus 2s and balance them out at nil.

    Hope this makes more sense. I'm still learning Excel terminology.
  • stragglebod
    stragglebod Posts: 1,324 Forumite
    First Post Name Dropper First Anniversary
    Kido wrote: »
    The result should be all the 2s minus all the -2s. So if both columns B&C (or D&E) have two number 2s in them I'd like the formula or pivot table to be able to extract all the 2s and minus 2s and balance them out at nil.

    Hope this makes more sense. I'm still learning Excel terminology.
    No, it now makes less sense than it did before.
  • that
    that Posts: 1,532 Forumite
    edited 15 October 2019 at 10:54AM
    stragglebod wrote: »
    No, it now makes less sense than it did before.
    I was going to say 'wow, how did you manage to work out that answer from the initial question", I was very impressed, then you typed this above:( I now realise you are confused as the rest of us

    Kido, is your spreadsheet look like my wine gum sheet of different size sweets? Please copy and amend this so it makes sense to us 
    Wine Gums   5g   8g   12g   16g   total
Green            11    16    20     26     41
Red               12    17    23     30     82
Black             19    23    32     40    114
  • Tom99
    Tom99 Posts: 5,371 Forumite
    First Post First Anniversary
    edited 15 October 2019 at 4:12AM
    Kido wrote: »
    I'm trying to work out a formula or Macro or something to split up a large excel spreadsheet containing about 5000 entries.

    Basically it has 6 columns, the first is the name, the second is the amount for one type, the third, fourth and fifth are an amounts for other types and the last one is the total of the previous 4 columns.

    What I'd like is to be able to (if possible) take all the figures from the final column which are e.g. 1s and add them together with all the -1s from the same column. This I'd then like to be repeated against all the numbers (e.g. 2s and -2s added together) and where the totals on specific amounts don't equal zero I'd like to be able to see easily.

    At the moment I'm having to do all this manually as my predecessor at work was terrified of playing with Excel where as I want to experiment and see what it can do.

    I hope someone can point me in the right direction.

    Thanks.
    Can you use the SumIf formula eg:
    =SUMIF(D4: D5004,"-2")+SUMIF(D4: D5004,"2")
    Will sum all the 2 and -2 in cells D4: D5004
    You can also use CountIf:
    =COUNTIF(D4: D5004,"-2")
    Will tell you the number of cells which equal -2 in range D4: D5004
  • WaywardDriver
    WaywardDriver Posts: 546 Forumite
    First Post First Anniversary
    stragglebod wrote: »
    No, it now makes less sense than it did before.
    How can amounts be negative?
  • DoaM
    DoaM Posts: 11,863 Forumite
    First Post First Anniversary Name Dropper Photogenic
    WaywardDriver wrote: »
    How can amounts be negative?

    Stock reduction? i.e. as stock is sold, such as when 2 items are taken the amount is -2 from the current stock holding.
  • WaywardDriver
    WaywardDriver Posts: 546 Forumite
    First Post First Anniversary
    DoaM wrote: »
    Stock reduction? i.e. as stock is sold, such as when 2 items are taken the amount is -2 from the current stock holding.
    But OP refers to amounts of types 1-4.
    Some context and an example couple of rows would help.
  • Bigphil1474
    Bigphil1474 Posts: 2,381 Forumite
    First Anniversary Name Dropper First Post Combo Breaker
    Op, if all the cells you want to add together are either 2 or -2, then a simple SUM function will work. e.g. =SUM(A1:A2) would return nil if one is 2 and the other is -2. Remember 2 minus -2 is 4, 2 plus -2 is 0.

    If you want to count the number of 2's and -2's, then COUNTIF is easier. I do a lot of relatively simple spreadsheets on excel, and the macro/pivot table stuff is well beyond me. I find it's often easier to have a number of extra columns that add, subtract etc. to give data I can work with, then hide those columns so they work in the back ground.
    If you are adding how many 1's or 2's in column six, etc. etc. then as tom #7 says should work. Once you create the correct formula for one cell, then you should be able to paste it for all the others.
This discussion has been closed.
★ ★ ★ Meet your Ambassadors

Categories

  • All Categories
  • 343K Banking & Borrowing
  • 250K Reduce Debt & Boost Income
  • 449.6K Spending & Discounts
  • 235.1K Work, Benefits & Business
  • 607.8K Mortgages, Homes & Bills
  • 173K Life & Family
  • 247.8K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.9K Discuss & Feedback
  • 15.1K Coronavirus Support Boards