Thread: Diagonal Summing -Averaging (Excel 2000)

1. Diagonal Summing -Averaging (Excel 2000)

Is there a way to sum numbers on a diagonal? And there is a twist. I have a table that presents data across columns for each month for the year and by month on the rows. The twist is that there are 2 rows of data for the months in a column. Or in other words, the table is 12 columns wide by 24 rows deep. So I would need a formula that would take a 6-month average, for example and assuming column headers in row 1 and row headers in column A, B3, C5, D7, E9, F11, G13.

Its not so bad figuring this for one table but I have to do several averages on several tables so a formula or macro solution would be sweet.

Have done some searching and have had little luck--Don't know if that extra row thing is going to hurt me.
Steve

2. Re: Diagonal Summing -Averaging (Excel 2000)

This will calc the average of the the diagonal starting in the upper left and working down

Steve
<pre>Function AvgDiag(rng As Range)
Dim x As Long
Dim lItems As Long
Dim dSum As Double
Dim lCount As Long

dSum = 0
lCount = 0
With rng
If .Columns.Count < .Rows.Count Then
lItems = .Columns.Count
Else
lItems = .Rows.Count
End If
For x = 1 To lItems
If Not (IsEmpty(.Cells(x, x))) Then
If IsNumeric(.Cells(x, x).Value) Then
dSum = dSum + .Cells(x, x).Value
lCount = lCount + 1
End If
End If
Next
End With
AvgDiag = dSum / lCount
End Function</pre>

3. Re: Diagonal Summing -Averaging (Excel 2000)

Steve, is you data laid out something like this, where the bold numbers are to be averaged?

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><tr><td align=center>4</td><td align=right>

4. Re: Diagonal Summing -Averaging (Excel 2000)

<P ID="edit" class=small>(Edited by sdckapr on 03-Dec-03 21:45. Fixed a flaw in the logic)</P>No my code does strictly the diagonal (col = row) [See Table]

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center valign=bottom>4</td><td align=right valign=bottom> </td><td align=right valign=bottom>1/1/2000</td><td align=right valign=bottom>2/1/2000</td><td align=right valign=bottom>3/1/2000</td><td align=right valign=bottom>4/1/2000</td><td align=center valign=bottom>5</td><td align=right valign=bottom>1/1/2000</td><td align=right valign=bottom>24014</td><td align=right valign=bottom>132111</td><td align=right valign=bottom>45902</td><td align=right valign=bottom>27007</td><td align=center valign=bottom>6</td><td align=right valign=bottom>1/1/2000</td><td align=right valign=bottom>14184</td><td align=right valign=bottom>124178</td><td align=right valign=bottom>55029</td><td align=right valign=bottom>32014</td><td align=center valign=bottom>7</td><td align=right valign=bottom>2/1/2000</td><td align=right valign=bottom>0</td><td align=right valign=bottom>18466</td><td align=right valign=bottom>142046</td><td align=right valign=bottom>36969</td><td align=center valign=bottom>8</td><td align=right valign=bottom>2/1/2000</td><td align=right valign=bottom>0</td><td align=right valign=bottom>17211</td><td align=right valign=bottom>109221</td><td align=right valign=bottom>62696</td><td align=center valign=bottom>9</td><td align=right valign=bottom>3/1/2000</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>30030</td><td align=right valign=bottom>109932</td><td align=center valign=bottom>10</td><td align=right valign=bottom>3/1/2000</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>51030</td><td align=right valign=bottom>94032</td><td align=center valign=bottom>11</td><td align=right valign=bottom>4/1/2000</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>14366</td><td align=center valign=bottom>12</td><td align=right valign=bottom>4/1/2000</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>83443</td></table>

Try this code, I added a second (optional) parameter (default is 1 which will calc as the previous example). The new code you would enter:
<pre>=avgdiag2(B5:E12,2)</pre>

Where the 2 indicates the number of rows to go down for each column (which is how your data will calc)

Steve

<pre>Function AvgDiag2(rng As Range, Optional lRows As Long = 1)
Dim iCol As Integer
Dim lRow As Long
Dim iCols As Integer
Dim dSum As Double
Dim lCount As Long
Dim lCurrRow As Long

dSum = 0
lCount = 0
lCurrRow = 1
With rng
If .Columns.Count < lRows * (.Rows.Count) Then
iCols = .Columns.Count
Else
iCols = lRows * (.Rows.Count)
End If
For iCol = 1 To iCols
For lRow = 1 To lRows
If Not (IsEmpty(.Cells(lCurrRow, iCol))) Then
If IsNumeric(.Cells(lCurrRow, iCol).Value) Then
dSum = dSum + .Cells(lCurrRow, iCol).Value
lCount = lCount + 1
End If
End If
lCurrRow = lCurrRow + 1
Next
Next
End With
AvgDiag2 = dSum / lCount
End Function</pre>

5. Re: Diagonal Summing -Averaging (Excel 2000)

Let me try to attach a table that highlights the cells to be added. I have to get an average of the percentage and then get a cumulative average.

6. Re: Diagonal Summing -Averaging (Excel 2000)

By the way, how do you guys get these tables in your messages?

Cool--I know I have seen it, but can't find out how.

7. Re: Diagonal Summing -Averaging (Excel 2000)

<img src=/S/grin.gif border=0 alt=grin width=15 height=15> Easy question first: see <post#=298697>post 298697</post#>.

8. Re: Diagonal Summing -Averaging (Excel 2000)

I see, it's every other row on the diagonal. Should the averages include zero value cells in the diagonal, or should they be excluded? What exactly is the cumulative average of; can you explain which cells would be counted in the cumul. average?

9. Re: Diagonal Summing -Averaging (Excel 2000)

This gets the average and matches yours.

<pre>Function AvgDiag3(rng As Range)
Dim iCol As Integer
Dim lRow As Long
Dim iCols As Integer
Dim dSum As Double
Dim lCount As Long
Dim lCurrRow As Long

dSum = 0
lCount = 0
lCurrRow = 1
With rng
If .Columns.Count < .Rows.Count Then
iCols = .Columns.Count
Else
iCols = .Rows.Count
End If
For iCol = 1 To iCols
If Not (IsEmpty(.Cells(lCurrRow, iCol))) Then
If IsNumeric(.Cells(lCurrRow, iCol).Value) Then
dSum = dSum + .Cells(lCurrRow, iCol).Value
lCount = lCount + 1
End If
End If
lCurrRow = lCurrRow + 2
Next
End With
AvgDiag3 = dSum / lCount
End Function</pre>

I second John's request for info to get the "Cumulative Average"

Steve

10. Re: Diagonal Summing -Averaging (Excel 2000)

On the spreadsheet, you can look where I was using the average function then below it, I actually just summed the averages to the the "cumulative" averages. Of course, that's the obvious answer, but these tables are coming to me from a person who has given notice and is leaving, so I am struggling just to figure out what they do, much less why. And I know a "why" would help you guys, whose help has already been fantastic, understand what I'm asking for better.

You have to look back at what I attached earlier. You should read the table by first looking at the Row labels. So the table says for all payments say, for January 2003, \$0 came in in January 03, \$46632 came in in the second month, Feb 03 or 12%, 67% in the 3rd month, etc. You will note that the last column always has an cumulative ROW average of 100%. What this function you have provided is doing, is saying, give me the FIRST MONTH average payment percentage for 6 months, on activity that occurred 11 months ago. Then I get the SECOND MONTH average on the same activity, and ADD that to the FIRST month (the CUMULATIVE PART). That sum tells me that after 2 months, I can expect ON AVERAGE that X% of activity is paid. I do that for 6 months, adding each month's average together, the first plus second through the 6th, to get an expectation of how much should be paid.

Ok, did that muddy the water?

11. Re: Diagonal Summing -Averaging (Excel 2000)

Boy, this REALLY sounds like an insurance completion factors lag analysis Steve K. That wouldn't be what it is by any chance? I work with those frequently.

If so I'd recommend that you quit alternating the amounts with the percentages, and work from a table with amounts only, with percentages below it in a duplicate table. I'm going to attach a spreadsheet where Sheet 1 shows how I develop the cumulative percentages FYI, though it doesn't directly answer your question as put.

Steve <!profile=sdckapr>sdckapr<!/profile> is in the best position to help you now with his UDF, though I did create a formula approach which does not count zero-result cells (changing it to include zero amount cells is very simple). Steve's UDF's and my formula are in Sheet 2.

12. Re: Diagonal Summing -Averaging (Excel 2000)

Bingo! I was trying to be discreet--don't really know why. And of course, I just got these and have VERY LITTLE IDEA of what I am doing, so I was trying to protect the IGNORANT (me).

I will take a look at what you have.

The problem with the layout initially is that I am a recipient of the table--don't know how much control I have over the layout. I can, of course, change the layout, but time is an issue right now (and you know I have deleted several columns and rows of data for the file I initially attached.)
Great to know I have a knowledgeable resource!!
Again guys, thanks a lot for the help.
Steve

13. Re: Diagonal Summing -Averaging (Excel 2000)

I'll help, when I can, with the format you get, though most of the 'triangles' I get are just the straight dollars paid data (such as in Sheet1 of my attachment). Having the percentages in the alternative rows the way they are in your table is just a nuisance. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

14. Re: Diagonal Summing -Averaging (Excel 2000)

Then you can explain what the tables are actually doing and how to "test" your estimation???? Cool <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Now, I feel dumb, and I am trying to solve this by reading Walkenbach's book, but I can't for the life of me get this function to work in other workbooks. I inserted the code into a module in my Personal.xls. I did get it to work by using the paste function thing but of course, it put the path in the function. I thought if it was in the Personal, it was open to all workbooks. I have had this problem before with UDF's and end up having to copy the sucker into all workbooks. Help?

15. Re: Diagonal Summing -Averaging (Excel 2000)

You can see a test of the "every other row" UDF and formula in sheet 2 under the word 'proof', where a series of =OFFSET() formulas are used to check just the first three diagonals. The same proof system can be used in sheet 1, but the shorthand proof in sheet1 is that the last cell in the single cumulative amounts row equals the total per the table.

To use a UDF from personal.xls you must always enter it as =personal.xls!MYUDF().

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•