1

I need to convert all values that have negative values into absolute values.

I'm working with the Kennedy Space Center to build a climatology based on 18 years worth of field mill data. Field mills measure electricity in the atmosphere values can range from -10k to 10K. The Space Center doesn't care if it goes below -1k or above 1k they just care when it reaches that threshold. I need to take 15 min averages but when doing this with average(abs() I get a very different value than just average. My solution is to convert everything to absolute value since the space center doesn't care about the direction the numbers are going. Below code I used to add columns after each field mill and other than adding a column next to all 34 columns then putting =abs(RC[-1]) I would need to do this for every column (29) for every value. Essentially this process will double the size of the spreadsheets. Instead I'd like to just replace the negative values with absolute values if that is possible.

Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=ABS(RC[-1])"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C13")
    Range("C2:C530000").Select
BigBen
  • 46,229
  • 7
  • 24
  • 40
Xane Jardineiro
  • 11
  • 1
  • You can use an array formula - `=AVERAGE(ABS(...))` and enter with Ctrl+Shift+Enter. – BigBen Oct 15 '19 at 12:56
  • BigBen, thank you for the response, the array formula has worked in the past however, when I average 134.2773333, 146.648, 177.8786667 the average is 152.934.... but the average(abs() gives me 146.648... hence my hesitation to not use this formula... – Xane Jardineiro Oct 15 '19 at 13:25
  • No repro. Are you sure you entered the array formula with Ctrl+Shift+Enter? https://i.stack.imgur.com/FQkAE.png – BigBen Oct 15 '19 at 13:27
  • Nope I'm an idiot... You were right.... thank you. – Xane Jardineiro Oct 15 '19 at 13:40
  • Not an idiot, just a simple mistake. Best of luck with the project! – BigBen Oct 15 '19 at 13:41

0 Answers0