We’d like to remind Forumites to please avoid political debate on the Forum.
This is to keep it a safe and useful space for MoneySaving discussions. Threads that are – or become – political in nature may be removed in line with the Forum’s rules. Thank you for your understanding.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
The Forum now has a brand new text editor, adding a bunch of handy features to use when creating posts. Read more in our how-to guide
Excel Help
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.
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.
0
Comments
-
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.0 -
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.0 -
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.0 -
No, it now makes less sense than it did before.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.0 -
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 usstragglebod wrote: »No, it now makes less sense than it did before.
Kido, is your spreadsheet look like my wine gum sheet of different size sweets? Please copy and amend this so it makes sense to usWine Gums 5g 8g 12g 16g total Green 11 16 20 26 41 Red 12 17 23 30 82 Black 19 23 32 40 114
0 -
Can you use the SumIf formula eg: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.
=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: D50040 -
How can amounts be negative?stragglebod wrote: »No, it now makes less sense than it did before.0 -
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.0 -
-
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.0
This discussion has been closed.
Confirm your email address to Create Threads and Reply
Categories
- All Categories
- 353.7K Banking & Borrowing
- 254.2K Reduce Debt & Boost Income
- 455.1K Spending & Discounts
- 246.8K Work, Benefits & Business
- 603.3K Mortgages, Homes & Bills
- 178.2K Life & Family
- 260.8K Travel & Transport
- 1.5M Hobbies & Leisure
- 16K Discuss & Feedback
- 37.7K Read-Only Boards