Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Thanks in advance,
    Steve

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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>
    -John ... I float in liquid gardens
    UTC -7ħDS

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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#>.
    -John ... I float in liquid gardens
    UTC -7ħDS

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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?
    -John ... I float in liquid gardens
    UTC -7ħDS

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #10
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ħDS

  12. #12
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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>
    -John ... I float in liquid gardens
    UTC -7ħDS

  14. #14
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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().
    -John ... I float in liquid gardens
    UTC -7ħDS

Posting Permissions

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