How Do I Create an Absolute Value Running Total

zotah

Board Regular
Joined
Feb 1, 2014
Messages
89
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
 
A Hundred or so times

I gave up after 20

Thanks for the formula anyway. At least it works

Zotah
 
Upvote 0
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...
 
Upvote 0
Hey...

Try this out...

Code:
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
 
Upvote 0
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
 
Upvote 0
Try this one..
forgot to add the (conversion of negative number to positive numbers) thingy

Code:
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
 
Upvote 0
Try this one..
forgot to add the (conversion of negative number to positive numbers) thingy

Code:
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top