How to add values in a column between selected rows

[RMassoudi]

Line	Area	Product (Avg.)	Set Marker
1		15	x
	30
2
3		25	x
	20
4		15	x
	10
5
6		5	x

<tbody>
</tbody>

Hello all,

This is my first post here, and I was wondering if anyone could help me do the following:

I am looking to try to perform a function where the values in the "area" column are taken and averaged into the "product" column based on the location of my "set marker".

For example, Line 1 has a marker whereas Line 2 does not - therefore the area value of 30 is average between Line 1 and Line 3 (which has a marker). Keep in mind that Line 3 also shares an area value with Line 4, etc. which also has a marker.

Any ideas would be really appreciated.

Thanks,
Rod

 

[onlyadrafter]

Hello,

THere may be a formula method, but have created a macro which works for your data set.

 Sub AVERAGE()
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("A" & MY_ROWS).Value > 0 Then
            For MY_NEXT_ROWS = MY_ROWS + 1 To Range("A" & Rows.Count).End(xlUp).Row
                If Range("D" & MY_NEXT_ROWS).Value = "x" Then
                    Range("C" & MY_ROWS).Value = Range("C" & MY_ROWS).Value + Range("B" & MY_ROWS + 1).Value / 2
                    Range("C" & MY_NEXT_ROWS).Value = Range("C" & MY_NEXT_ROWS).Value + Range("B" & MY_ROWS + 1).Value / 2
                    GoTo CONT
                End If
            Next MY_NEXT_ROWS
        End If
CONT:
    Next MY_ROWS
End Sub

 

[RMassoudi]

Thank you! That's pretty brilliant.

I was wondering if there was anyway that I could place an "area" value between two hypothetical lines, say between lines 6 and 12, and have an "X" at 6 and 12, and distribute that value to those two... See pic: https://www.dropbox.com/s/i2in7g8jv5pihs6/1.JPG?dl=0

 

[onlyadrafter]

Hello,

How you do you determine how the 'area' is shared out?

 

[RMassoudi]

Hello,

How you do you determine how the 'area' is shared out?

Between the lines denoted with an "X". If I have a value of 20 above one X and below one X - I would distribute 10 to each. If there's another value above or below either of those X'd lines, then they would be added on as well. However, if there's a value above a line with an X or below a line with an X and there's no other X to sandwich it, the full value will be transferred to that X'd line.

 

[RMassoudi]

Between the lines denoted with an "X". If I have a value of 20 above one X and below one X - I would distribute 10 to each. If there's another value above or below either of those X'd lines, then they would be added on as well. However, if there's a value above a line with an X or below a line with an X and there's no other X to sandwich it, the full value will be transferred to that X'd line.

Bump

 

[onlyadrafter]

Hello,

Sub AVERAGE_AREA()
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("D" & MY_ROWS).Value = "x" Then
            For MY_NEXT_ROWS = MY_ROWS + 1 To Range("A" & Rows.Count).End(xlUp).Row
                If Range("B" & MY_NEXT_ROWS).Value > 0 Then
                        MY_NUMBER = Range("B" & MY_NEXT_ROWS)
                End If
                If Range("D" & MY_NEXT_ROWS).Value = "x" Then
                    Range("C" & MY_ROWS).Value = Range("C" & MY_ROWS).Value + MY_NUMBER / 2
                    Range("C" & MY_NEXT_ROWS).Value = Range("C" & MY_NEXT_ROWS).Value + MY_NUMBER / 2
                    GoTo CONT
                End If
            Next MY_NEXT_ROWS
        End If
CONT:
    Next MY_ROWS
End Sub

 

[RMassoudi]

Hello,

Sub AVERAGE_AREA()
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("D" & MY_ROWS).Value = "x" Then
            For MY_NEXT_ROWS = MY_ROWS + 1 To Range("A" & Rows.Count).End(xlUp).Row
                If Range("B" & MY_NEXT_ROWS).Value > 0 Then
                        MY_NUMBER = Range("B" & MY_NEXT_ROWS)
                End If
                If Range("D" & MY_NEXT_ROWS).Value = "x" Then
                    Range("C" & MY_ROWS).Value = Range("C" & MY_ROWS).Value + MY_NUMBER / 2
                    Range("C" & MY_NEXT_ROWS).Value = Range("C" & MY_NEXT_ROWS).Value + MY_NUMBER / 2
                    GoTo CONT
                End If
            Next MY_NEXT_ROWS
        End If
CONT:
    Next MY_ROWS
End Sub

This is awesome! Thank you. My apologies for looking a gift horse in the mouth, but is there anyway I can do this with formulas? I'm okay using an assortment of additional columns, etc.