0

I have a sheet that looks something like this:

Sheet 1

    1   2   3   4   5   6   7   8   9   10  11
1                                           6
2                                       3   5
3                                           
4                               2   4   9   4
5                                           
6                                   4   6   6
7       5   3   3       3   10  8   4       8
8                                           
9                           4   11  12  12  6
10                                          
11  8   5   5       4   9   4   7   6       

What I would like to be able to do is find the average difference and direction between values in each column for each row. For example, the first 4 rows would look like:

     Average Difference # + Movements   # -Movements
1           
2           2                    1              0
3           
4       (2+5+5)/3                2              1

Blanks represent N/A values due to insufficient information, and differences are calculated successively i.e. col2-col1, col3-col2, col4-col3

If I just take the differences and make a duplicate table with the formula =C2-B2 copied across issues arise whenever there is a blank space between two values or at the beginning of the row. Is there an easy way to fix this or another way to do this that I might be missing?

2
  • It's not really clear what you are trying to do. What would the result of this table look like? Are you subtracting row 3 from row 2 and then row 4 from row 3. We need more details before we can help you.
    – gtwebb
    Aug 20, 2014 at 21:20
  • @gtwebb Thanks gtwebb, I've updated the post with a few more details that will hopefully help clarify the situation.
    – 114
    Aug 20, 2014 at 21:45

3 Answers 3

1

If you want a VBA solution, copy this into a module.

Function Score(R As Range, Col As String)
    Dim ThisCell As Range
    Dim Dif As Integer
    Dim Cnt As Integer
    Dim PosMove As Integer
    Dim NegMove As Integer
    Dim PrevNum As Integer
    Dim ThisNum As Integer
    PrevNum = 9999
    For Each ThisCell In R.Cells
        If IsNumeric(ThisCell.Text) Then
            ThisNum = ThisCell.Value
            If PrevNum <> 9999 Then
                Cnt = Cnt + 1
                If ThisNum > PrevNum Then
                    Dif = Dif + (ThisNum - PrevNum)
                    PosMove = PosMove + 1
                ElseIf ThisNum < PrevNum Then
                    Dif = Dif + (PrevNum - ThisNum)
                    NegMove = NegMove + 1
                End If
            End If
            PrevNum = ThisNum
        End If
    Next
    Select Case LCase(Col)
        Case "avg"
            If Dif = 0 Or Cnt = 0 Then
                Score = 0
            Else
                Score = Dif / Cnt
            End If
        Case "pos"
            Score = PosMove
        Case "neg"
            Score = NegMove
    End Select
End Function

Then you would make the call like this:

=score(A1:K1,"avg")
=score(A1:K1,"pos")
=score(A1:K1,"neg")
0
1

I would use an if statement to check if the cells are filled

=IF(COUNT(B2:B3)=2,B3-B2,"")

If you then average the row the blank cells will get ignored and you should get the value you want.

For your table below. Average change would be

=SUMPRODUCT(--(A2:C2<>""),--(A3:C3<>""),(A3:C3-A2:C2))/SUMPRODUCT(--(A2:C2<>""),--(A3:C3<>""))

Number of cells that increase could be

=SUMPRODUCT(--(A2:C2<A3:C3))

Number of cells decreasing would be

=SUMPRODUCT(--(A2:C2>A3:C3))

This assumes the table is only 3 columns wide, expand for your data.

Sumproduct is mainly used to do array calculations. You could also use other formulas using ctrl+shift+enter but these ones should do the trick.

3
  • Sorry, I mentioned I was interested in the rows when I should have been looking at the columns. Simply changing B2:B3 to B2:C2 and A2:C2 to A2:A4 should work, is that right?
    – 114
    Aug 20, 2014 at 22:20
  • Yes that's correct
    – gtwebb
    Aug 20, 2014 at 22:37
  • The only thing I need to adjust is that if there is a blank space and then another number after the blank space that difference would also need to be calculated, but is omitted in that formula since the count =/= 2.
    – 114
    Aug 21, 2014 at 15:16
0

I would use the Power Query Add-In for this. I've built a prototype which you can view or download - its "Power Query demo - average movement and difference across a row.xlsx" in my One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Power Query has an UnPivot command to turn rows into columns (which ignores empty cells) and you can add an Index column to keep track of the relative position of cells. In my 1st Query I used those commands to transform your table into a long list of rows (one for each cell).

Then in the 2nd Query I Merged that list with itself to relate each cell value to the next non-blank cell to the left. Then the calculations e.g. Differences , + Movements became fairly easy. The last step is to Group By row number and aggregate the calculations

Finally I started again from the Input data and Merged the 2nd Query, to get the full list of rows (including those without differences).

Then benefit of the Power Query approach is that you dont need to code in VBA, 99% of it is done by clicking around in the Power Query UI, or by easy edits to generated code. You also dont need complex nested formulas - you can reshape the data until the calculations become easy.

The Power Query Unpivot command is a wonder - it will automatically expand to cater to new columns if they are added to the source table, and remove blank values.

4
  • I would love to do this but it is no longer included in my version of Excel after upgrading to Excel 2013.
    – 114
    Aug 21, 2014 at 14:58
  • Power Query is an Add-In that you need to download and install eg from: microsoft.com/en-au/download/details.aspx?id=39379
    – Mike Honey
    Aug 21, 2014 at 23:06
  • I've built a prototype and added the link and an updated description into my answer above.
    – Mike Honey
    Aug 22, 2014 at 3:53
  • Yes, but that Add-In can only be used with certain versions of Excel 2013. The same thing happened with PowerPivot.
    – 114
    Aug 22, 2014 at 13:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .