How Do I Create an Absolute Value Running Total

[zotah]

How Do I Create an Absolute Value Running Total or that's what I think it's called.

Let's say I have a column of numbers like this

5
2
8
-9
-5
-15
4
8
7
-6
-4
-2
-8
5
-4
7

and I want to have a running total in the cell directly to the right of these numbers that did a running total average of each number where the formula will figure out that it will translate all the negative (-) numbers are to be read as positive numbers and formulating it's average so all the negative number will be counted as a positive number like this

5
2
8
9
5
15
4
8
7
6
4
2
8
5
4
7

then the formula will give a running average of the first 2 cells of the numbers 5 and 2 and then the next cell to the left that says 5 and 2 and 8 will give the running total of those 3 numbers written in the cell directly to the right of each number listed. and then the average will be given of the absolute numbers will list the average of the first 4 numbers listed of 5 and 2 and 8 and 9 etc.

This formula should be able to be dragged down the excel page so it wouldn't matter how long the list of number were it will give the absolute running total numbers all the way down.

I'm not that good in understanding the math side of these problems. So I'm sure I'm not explaining to the right way. But I hope you understand what I mean.

So

Number 1) All the numbers listed in Cell A will be read in the formula Cell B as positive numbers only.

Number 2) All the averages are in a running total down Cell B and the averages are based on each number combination of the total numbers above it only.

Example

Cell 1) 5 (Taking the average of the number 5 only = 5)
Cell 2) 2 (Taking the average of both the number 5 and the number 3 =)
Cell 3) 8 (Taking the average of all 3 numbers of the 5 and 3 and 8)
Cell 4) 9 (Taking the average of all 4 numbers of the 5 and 3 and 8 and 9)
Cell 5) 5 (Taking the average of all 5 numbers of the 5 and 3 and 8 and 9 and 5) Etc. Etc.

I hope I explained what I'm trying to do correctly.

Thanks for any help you can give me on this to create the formula I need,

Zotah

 

[zotah]

A Hundred or so times

I gave up after 20

Thanks for the formula anyway. At least it works

Zotah

 

[juz2gud]

hmmmm,,, giv me 20 mins

 

[zotah]

Sound Good

Start cooking

 

[juz2gud]

sorry mate, i couldnt crack that in VBA to make it continue throughout the column..
it doesnt update the 3 vaues in the formula that you have to do...

 

[zotah]

sorry mate, i couldnt crack that in VBA to make it continue throughout the column..
it doesnt update the 3 vaues in the formula that you have to do...

Maybe next time,

But thanks for trying

zotah

 

[juz2gud]

Hey...

Try this out...

Sub Macro1()
'
' Macro1 Macro
'Keyboard Shortcut: Ctrl+Shift+A
    
    Dim aFirstRange As String
    Dim aSecondRange As String
    Dim aFormula As String
    Dim bfirstrange As String
    
Line1:
    ActiveCell.Offset(0, -1).Range("A1").Select
    aFirstRange = ActiveCell.Row
    aSecondRange = ActiveCell.Column
    aFormula = "=AVERAGE(R" + aFirstRange + "C" + aSecondRange + ":" + "R[0]C[-1])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = aFormula
    ActiveCell.Offset(1, 0).Range("A1").Select
       
    If ActiveCell.Offset(0, -1).Range("A1").Value <> "" Then
        Do While ActiveCell.Offset(0, -1).Range("A1").Value <> ""
        If ActiveCell.Offset(0, -1).Range("A1").Value = "" Then
        Exit Do
        End If
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select
    Loop
    
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
        
        If ActiveCell.Offset(0, -1).Range("A1").Value <> "" Then
        GoTo Line1
        Else:
            Do
            Selection.End(xlUp).Select
            bfirstrange = ActiveCell.Row
            Loop Until bfirstrange = 1
            Selection.End(xlDown).Select
            Selection.End(xlToRight).Select
            End If
                
                If ActiveCell.Column <> 16384 Then
                ActiveCell.Offset(0, 1).Range("A1").Select
                GoTo Line1
                Else:
                Selection.End(xlToLeft).Select
                MsgBox ("B I N G O ! ! ! !")
                End If
    Else:
    Selection.End(xlToLeft).Select
    MsgBox ("B I N G O ! ! ! !")
    End If
    End Sub

Shortcut is CTRL SHFT A

and All you need to do is, click the cell to the RIGHT of the FIRST column and FIRST row that contains data..

for instance,,, if your data is in column B D and G.. and it all starts from row 2... you need to click the cell C2 and hit CTRL SHFT A ... and let Excel do it all.


Do lemme know if this worked for u..
i couldnt sleep properly for the past few days.. coz of failing to this ****ty thing lol.. im positive we r done here, but do check and confirm

 

[juz2gud]

and yeah, forgot to mention

Assumptions:
1) ALL of your data starts from the SAME row (columns may differ, but row should be same).. Means if your first data is in column B starting from B2,, your other data in column f h j k whtever, should start from F2 H2 J2 K2
2) ALL your data is in one sheet...
if you ave in other sheet too, yhou just have to click the cell next to first column and hit CTRL SHFT A again

 

[juz2gud]

Try this one..
forgot to add the (conversion of negative number to positive numbers) thingy

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
     
    Dim aFirstRange As String
    Dim aSecondRange As String
    Dim aFormula As String
    Dim bfirstrange As String
    
Line1:
    If ActiveCell.Offset(0, -1).Range("A1").Value < 1 Then
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = ActiveCell.Value * -1
    ActiveCell.Offset(0, 1).Range("A1").Select
    End If
    ActiveCell.Offset(0, -1).Range("A1").Select
    aFirstRange = ActiveCell.Row
    aSecondRange = ActiveCell.Column
    
    aFormula = "=AVERAGE(R" + aFirstRange + "C" + aSecondRange + ":" + "R[0]C[-1])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = aFormula
    ActiveCell.Offset(1, 0).Range("A1").Select
       
    If ActiveCell.Offset(0, -1).Range("A1").Value <> "" Then
        Do While ActiveCell.Offset(0, -1).Range("A1").Value <> ""
        If ActiveCell.Offset(0, -1).Range("A1").Value = "" Then
        Exit Do
        End If
    If ActiveCell.Offset(0, -1).Range("A1").Value < 1 Then
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = ActiveCell.Value * -1
    ActiveCell.Offset(0, 1).Range("A1").Select
    End If
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select
    Loop
    
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
        
        If ActiveCell.Offset(0, -1).Range("A1").Value <> "" Then
        GoTo Line1
        Else:
            Do
            Selection.End(xlUp).Select
            bfirstrange = ActiveCell.Row
            Loop Until bfirstrange = 1
            Selection.End(xlDown).Select
            Selection.End(xlToRight).Select
            End If
                
                If ActiveCell.Column <> 16384 Then
                ActiveCell.Offset(0, 1).Range("A1").Select
                GoTo Line1
                Else:
                Selection.End(xlToLeft).Select
                MsgBox ("B I N G O ! ! ! !")
                End If
    Else:
    Selection.End(xlToLeft).Select
    MsgBox ("B I N G O ! ! ! !")
    End If
End Sub

 

[zotah]

Try this one..
forgot to add the (conversion of negative number to positive numbers) thingy

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
     
    Dim aFirstRange As String
    Dim aSecondRange As String
    Dim aFormula As String
    Dim bfirstrange As String
    
Line1:
    If ActiveCell.Offset(0, -1).Range("A1").Value < 1 Then
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = ActiveCell.Value * -1
    ActiveCell.Offset(0, 1).Range("A1").Select
    End If
    ActiveCell.Offset(0, -1).Range("A1").Select
    aFirstRange = ActiveCell.Row
    aSecondRange = ActiveCell.Column
    
    aFormula = "=AVERAGE(R" + aFirstRange + "C" + aSecondRange + ":" + "R[0]C[-1])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = aFormula
    ActiveCell.Offset(1, 0).Range("A1").Select
       
    If ActiveCell.Offset(0, -1).Range("A1").Value <> "" Then
        Do While ActiveCell.Offset(0, -1).Range("A1").Value <> ""
        If ActiveCell.Offset(0, -1).Range("A1").Value = "" Then
        Exit Do
        End If
    If ActiveCell.Offset(0, -1).Range("A1").Value < 1 Then
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = ActiveCell.Value * -1
    ActiveCell.Offset(0, 1).Range("A1").Select
    End If
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select
    Loop
    
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
        
        If ActiveCell.Offset(0, -1).Range("A1").Value <> "" Then
        GoTo Line1
        Else:
            Do
            Selection.End(xlUp).Select
            bfirstrange = ActiveCell.Row
            Loop Until bfirstrange = 1
            Selection.End(xlDown).Select
            Selection.End(xlToRight).Select
            End If
                
                If ActiveCell.Column <> 16384 Then
                ActiveCell.Offset(0, 1).Range("A1").Select
                GoTo Line1
                Else:
                Selection.End(xlToLeft).Select
                MsgBox ("B I N G O ! ! ! !")
                End If
    Else:
    Selection.End(xlToLeft).Select
    MsgBox ("B I N G O ! ! ! !")
    End If
End Sub

Got It

I appreciate all the work you put into this.

Zotah

 

[juz2gud]

No problem mate, juz let me know if it giveing problem