muck Posted May 15, 2009 Share Posted May 15, 2009 Is there any quick way to take a long string of numbers and add up the absolute value of those numbers without creating a new column/row to first find the ABS of each individual number? Sorta like "SumProduct"? Quote Link to comment Share on other sites More sharing options...
Big John Posted May 15, 2009 Share Posted May 15, 2009 Create a new column with a field of @ABS(cell in other field) in each field and sum up the numbers in this column? Quote Link to comment Share on other sites More sharing options...
muck Posted May 15, 2009 Author Share Posted May 15, 2009 Create a new column with a field of @ABS(cell in other field) in each field and sum up the numbers in this column? Doing that would be a REAL p.i.t.a ... presumably there is no other way, eh? Quote Link to comment Share on other sites More sharing options...
Brentastic Posted May 15, 2009 Share Posted May 15, 2009 Doing that would be a REAL p.i.t.a ... presumably there is no other way, eh? Why would it be such a p.i.t.a. to add a new column? Would take you 1 minute. Quote Link to comment Share on other sites More sharing options...
westvirginia Posted May 15, 2009 Share Posted May 15, 2009 How big is your column? You can do like this: =ABS(A1)+ABS(A5)+ABS(A3)+ABS(A2)+ABS(A4) but then you get into character limitations. You can't just create the column, then hide it? Or even create a second tab in the worksheet? Quote Link to comment Share on other sites More sharing options...
muck Posted May 15, 2009 Author Share Posted May 15, 2009 How big is your column? You can do like this: =ABS(A1)+ABS(A5)+ABS(A3)+ABS(A2)+ABS(A4) but then you get into character limitations. You can't just create the column, then hide it? Or even create a second tab in the worksheet? ...that's basically what I did...there were 168 groups of 10 numbers spread across six sheets in a workbook...hence the reason that I said that adding a column would be a PITA (formatting = ugh). Quote Link to comment Share on other sites More sharing options...
whomper Posted May 15, 2009 Share Posted May 15, 2009 Use the quadratic formula Quote Link to comment Share on other sites More sharing options...
Big Country Posted May 15, 2009 Share Posted May 15, 2009 ...that's basically what I did...there were 168 groups of 10 numbers spread across six sheets in a workbook...hence the reason that I said that adding a column would be a PITA (formatting = ugh). Were all the numbers in a single column on each sheet? If so, I believe you could have selected the multiple worksheets, entered the formula in a new column on just one worksheet and it copies over. Take maybe 5 minutes to do. Quote Link to comment Share on other sites More sharing options...
CaP'N GRuNGe Posted May 15, 2009 Share Posted May 15, 2009 Found it... Enter the formula below: =SUM(ABS(A1:A10)) and then hit Ctrl+Shift+Enter It will work and make your formula look like this: {=SUM(ABS(A1:A10))} Quote Link to comment Share on other sites More sharing options...
westvirginia Posted May 15, 2009 Share Posted May 15, 2009 Found it... Enter the formula below: =SUM(ABS(A1:A10)) and then hit Ctrl+Shift+Enter It will work and make your formula look like this: {=SUM(ABS(A1:A10))} Wow, good on you grungie. It doesn't work if you type it out with the brackets, just follow grunge's instructions though and it will work. Quote Link to comment Share on other sites More sharing options...
CaP'N GRuNGe Posted May 15, 2009 Share Posted May 15, 2009 Wow, good on you grungie. It doesn't work if you type it out with the brackets, just follow grunge's instructions though and it will work. Google is your friend. Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted May 15, 2009 Share Posted May 15, 2009 Found it... Enter the formula below: =SUM(ABS(A1:A10)) and then hit Ctrl+Shift+Enter It will work and make your formula look like this: {=SUM(ABS(A1:A10))} Wow! Nice. I tried the formula but never though of the Ctrl+Shift+Enter. Great to know. Quote Link to comment Share on other sites More sharing options...
westvirginia Posted May 15, 2009 Share Posted May 15, 2009 Google is your friend. Well, MS Excel help is NOT friendly! Not a bit. Quote Link to comment Share on other sites More sharing options...
Brentastic Posted May 15, 2009 Share Posted May 15, 2009 Found it... Enter the formula below: =SUM(ABS(A1:A10)) and then hit Ctrl+Shift+Enter It will work and make your formula look like this: {=SUM(ABS(A1:A10))} This must be a joke. THat's the first thing I tried and it errors out. Quote Link to comment Share on other sites More sharing options...
muck Posted May 15, 2009 Author Share Posted May 15, 2009 This must be a joke. THat's the first thing I tried and it errors out. ...works for me... Did you ctl-shft-entr? ...oh, and thanks, Mr. Grunge... Quote Link to comment Share on other sites More sharing options...
Brentastic Posted May 15, 2009 Share Posted May 15, 2009 ...works for me... Did you ctl-shft-entr? ...oh, and thanks, Mr. Grunge... Ok, now I know it's a joke. I guess I was late to the party on that one. Quote Link to comment Share on other sites More sharing options...
muck Posted May 15, 2009 Author Share Posted May 15, 2009 Ok, now I know it's a joke. I guess I was late to the party on that one. :LeftHandOnBibleRightHandInTheAir: It works for me. Quote Link to comment Share on other sites More sharing options...
westvirginia Posted May 15, 2009 Share Posted May 15, 2009 :LeftHandOnBibleRightHandInTheAir: It works for me. +1 Quote Link to comment Share on other sites More sharing options...
Big Country Posted May 15, 2009 Share Posted May 15, 2009 Ok, now I know it's a joke. I guess I was late to the party on that one. It works. It is an array formula. Here is a good link describing them Quote Link to comment Share on other sites More sharing options...
Brentastic Posted May 15, 2009 Share Posted May 15, 2009 Wow, you guys weren't sh!tting!! Thanks for the link! Quote Link to comment Share on other sites More sharing options...
Brentastic Posted May 15, 2009 Share Posted May 15, 2009 I'm seriously stoked about this revelation. Me and my work buddy nerds are totally in awe of these array formulas. It essentially allows you to create any formula that excel has not thought of yet - that's sweet!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.