Cumulative formula based on entries

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

Cumulative Formula Based On Entries

Rate this: (3/5 from 1 vote)

	I 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 v4mpy on 24 Feb 2006

Replies - Displaying 1 to 1 of 1

Order Replies By: Most Recent | Chronological | Highest Rated

	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:   I J K L 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 Excel Helper on 07 Mar 2006

Displaying page 1 of 1