Home > Forum Home > Automating Data Analysis Tasks > Cumulative formula based on entries Share

Cumulative formula based on entries

Excel Help for Cumulative Formula Based On Entries in Automating Data Analysis Tasks


Forum TopicPost Reply Login

Cumulative Formula Based On Entries

Rate this:
(3/5 from 1 vote)
ConfusedI need help, Formula in L4 is fine as below. I'm trying to get L6, so after W
is inserted into I6 instead of adding up H4-H6 it starts adding up at H7 down
until next W is inserted, then start process again. I would like a formula that
doesn't matter where L or W are: it justs starts adding from the last W for
column I.
=IF(ISERROR(IF(I4="W",-H4,H4*E4/G4)),"-",IF(I4="L",-H4,H4*E4/G4))    


B        E      G      H        I      J      K        L
4  2.5      6      4    2.50      L      5      7.5    -2.5
5  7.5      5      2    3.75      L  11.25  13.75  -6.25
6  13.75    6    2      5        W    -        -      8.75
7  2.5      7    2      1.25    L    3.75  6.25  -1.25

I hope this all makes sense.

Thanks
 Posted by on
 
Replies - Displaying 1 to 1 of 1Order Replies By: Most Recent | Chronological | Highest Rated
Shocked
Data Filter with Subtotals
Rate this:
(3/5 from 1 vote)
You could try to filter your data and then enter your formula as a subtotal.  You would need to distinquish each group of L's spearately in column I.

Alternatively, you could use some VBA code to run the cumulative sums between the W values.

Excel Spreadsheet:
 IJKL
1    
2 L 5 7.5 7.5
3 L 11.25 13.75 21.25
4 W - - 
5 L 3.75 6.25 6.25
6    


Assuming you wanted to sum on column K you could select the cells in column L and run the following VBA code:

VBA Code:
Sub Sum_I()
Dim tempsum As Single
Dim a As Variant
tempsum = 0
For Each a In Selection
    If Trim(a.Offset(0, -3).Value) = "L" Then
        tempsum = tempsum + a.Offset(0, -1).Value
        a.Formula = tempsum
    Else
        tempsum = 0
    End If
Next a
End Sub

 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Cumulative formula based on entries in the

© Business Spreadsheets   Legal   Follow us: