Diagonal Sum in excel table via VBA code

[spgexcel]

Hello,
Hope you are all good.
I am having a trouble with a diagonal sum vba code. Not sure what wrong I am doing.
1st will explain what I am trying to do.
In the image attached herewith is a table. (A9:F13)

• Rows A10, A11, A12 should multiply with their respective curve from the curve library and sum them diagonally.
• The multiplication should occur with the correct row and column values, ensuring the diagonal sum is cumulative.

Key Requirements:​

1. Row 10: A10 * Curve(ABC, M1)
2. Row 11: A11 * Curve(CDE, M1) + A10 * Curve(CDE, M2)
3. Row 12: A12 * Curve(CDE, M1) + A11 * Curve(CDE, M2) + A10 * Curve(CDE, M3)

I am expecting a result as in 2nd table (A15:F18)

The VBA incorrectly references to $A10 in 2nd row whereas it should reference to $A10.
Formula text table is also mentioned for reference.
Output column is where diagonal sum is expected. Have color coded the output column and table cell to show what cells are summed up to get output result.

Here is my vba code.

Sub CalculateDiagonalSum()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim result As Double
    Dim numberValue As Double, curveValue As Double
    Dim curveRow As Range
    Dim curveColIndex As Integer
    Dim curveName As String
    Dim rowOffset As Integer
    
 
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
  
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    

    ws.Range("F10:F" & lastRow).ClearContents

    For i = 10 To lastRow
        result = 0
        curveName = ws.Cells(i, "B").Value
        
     
        Set curveRow = ws.Range("B4:B6").Find(What:=curveName, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not curveRow Is Nothing Then
     
            For j = 0 To i - 10
                rowOffset = i - j
                numberValue = ws.Cells(rowOffset, "A").Value
                curveColIndex = 3 + j
                
                If curveColIndex <= 5 Then
                    curveValue = ws.Cells(curveRow.Row, curveColIndex).Value
                    result = result + (numberValue * curveValue)
                End If
            Next j
        End If

        ws.Cells(i, "F").Value = result
    Next i
    
End Sub

Hoping to resolve this as this is an example table. I am trying to do away the need of creating a big table in my model and just do a vba based diagonal sum. My model has huge table.
Hope you can help me with it.



Thanks

Sumant

 

[myall_blues]

See if this works for you. My numbers came out slightly different - for example value at C10 on your sheet comes to 75.98 instead of 76.25 as in your example, but my manual calculation of 58*131%=75.98. Perhaps there's some additional decimals in your percentages that I can't see?

Sub CalculateDiagonalSum()
    Dim ws As Worksheet
    Dim Result As Double
    Dim lastrow As Long, i As Long, j As Long, k As Long
    Dim CurveArr, OutputArr
    '
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    CurveArr = ws.Range("C5:E6")
    ws.Range("F10:F" & lastrow).ClearContents
    OutputArr = ws.Range("A10:F" & lastrow)
    For i = LBound(OutputArr, 1) To UBound(OutputArr, 1)
        For j = 3 To 5
            Select Case OutputArr(i, 2)
                Case "ABC"
                    OutputArr(i, j) = OutputArr(i, 1) * CurveArr(1, j - 2)
                Case "CDE"
                    OutputArr(i, j) = OutputArr(i, 1) * CurveArr(2, j - 2)
                Case Else
                    MsgBox "Error"
                    Exit Sub
            End Select
        Next j
    Next i
    '
    For i = UBound(OutputArr, 1) To LBound(OutputArr, 1) Step -1
        k = 0
        For j = 3 To i + 2
            OutputArr(i, UBound(OutputArr, 2)) = OutputArr(i, UBound(OutputArr, 2)) + OutputArr(i - k, j)
            k = k + 1
        Next j
    Next
    '
    Range("A15").Resize(UBound(OutputArr, 1), UBound(OutputArr, 2)) = OutputArr
End Sub

 

[spgexcel]

See if this works for you. My numbers came out slightly different - for example value at C10 on your sheet comes to 75.98 instead of 76.25 as in your example, but my manual calculation of 58*131%=75.98. Perhaps there's some additional decimals in your percentages that I can't see?

Sub CalculateDiagonalSum()
    Dim ws As Worksheet
    Dim Result As Double
    Dim lastrow As Long, i As Long, j As Long, k As Long
    Dim CurveArr, OutputArr
    '
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    CurveArr = ws.Range("C5:E6")
    ws.Range("F10:F" & lastrow).ClearContents
    OutputArr = ws.Range("A10:F" & lastrow)
    For i = LBound(OutputArr, 1) To UBound(OutputArr, 1)
        For j = 3 To 5
            Select Case OutputArr(i, 2)
                Case "ABC"
                    OutputArr(i, j) = OutputArr(i, 1) * CurveArr(1, j - 2)
                Case "CDE"
                    OutputArr(i, j) = OutputArr(i, 1) * CurveArr(2, j - 2)
                Case Else
                    MsgBox "Error"
                    Exit Sub
            End Select
        Next j
    Next i
    '
    For i = UBound(OutputArr, 1) To LBound(OutputArr, 1) Step -1
        k = 0
        For j = 3 To i + 2
            OutputArr(i, UBound(OutputArr, 2)) = OutputArr(i, UBound(OutputArr, 2)) + OutputArr(i - k, j)
            k = k + 1
        Next j
    Next
    '
    Range("A15").Resize(UBound(OutputArr, 1), UBound(OutputArr, 2)) = OutputArr
End Sub

@myall_blues : Awesome, This is exactly what I wanted.. I can not thank you enough. And yes it was a matter of decimals. In my workbook I get exact result..

 

[myall_blues]

You’re welcome. Thanks for the feedback.

 

[spgexcel]

Hi @myall_blues ,

I modified the code a bit to do away the hard coding of the curve names,

Sub CalculateDiagonalSumNew()
    Dim ws As Worksheet
    Dim Result As Double
    Dim lastrow As Long, i As Long, j As Long, k As Long
    Dim CurveArr As Variant, OutputArr As Variant
    Dim CurveName As String
    Dim CurveRow As Long
   
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
   
    ' Get the last row
    lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
    ws.Range("F10:F" & lastrow).ClearContents
   
   
    ' Load curve library and output data into arrays
    CurveArr = ws.Range("C5:E7").Value
    OutputArr = ws.Range("A10:F" & lastrow).Value
       
 
    For i = LBound(OutputArr, 1) To UBound(OutputArr, 1)
        CurveName = OutputArr(i, 2)
        CurveRow = 0
       
    For j = 1 To UBound(CurveArr, 1)
            If ws.Cells(4 + j, 2).Value = CurveName Then
                CurveRow = j
                Exit For
            End If
        Next j
       
        ' If no match is found, show an error
        If CurveRow = 0 Then
            MsgBox "Error: Curve name '" & CurveName & "' not found in curve library."
            Exit Sub
        End If
       
For j = 3 To 5
            OutputArr(i, j) = OutputArr(i, 1) * CurveArr(CurveRow, j - 2)
        Next j
    Next i
   
    ' Calculate the diagonal sum
    For i = UBound(OutputArr, 1) To LBound(OutputArr, 1) Step -1
        k = 0
        For j = 3 To i + 2
            OutputArr(i, UBound(OutputArr, 2)) = OutputArr(i, UBound(OutputArr, 2)) + OutputArr(i - k, j)
            k = k + 1
        Next j
    Next
       
    ' Output only the final results to F10:F12
    For i = LBound(OutputArr, 1) To UBound(OutputArr, 1)
        ws.Cells(i + 9, 6).Value = OutputArr(i, UBound(OutputArr, 2))
    Next i
   

End Sub

It works fine. But the moment I try to change the range of output to c column it messes up the things. Tried to debug but gives weird results.

Basically, my original sheet has big table of curve library and long list of numbers to multiply and do the diagonal sum of.
Please refer to the attached excel sheet. I hit the run button, and get the curve not found error while it is there in the curve library.

Here is actual data lets say:

Curve Name

M1

M2

M3

M4

M5

M6

M7

M8

M9

M10

M11

M12

M13

M14

M15

M16

M17

M18

M19

M20

M21

M22

M23

M24

AB

131%​

124%​

116%​

75%​

60%​

40%​

30%​

24%​

20%​

17%​

17%​

15%​

13%​

13%​

12%​

12%​

12%​

11%​

10%​

9%​

8%​

8%​

8%​

8%​

BC

133%​

117%​

104%​

65%​

51%​

48%​

40%​

30%​

28%​

24%​

21%​

18%​

15%​

14%​

13%​

12%​

12%​

11%​

10%​

9%​

8%​

8%​

7%​

7%​

CD

133%​

107%​

95%​

61%​

51%​

42%​

35%​

29%​

25%​

22%​

20%​

17%​

13%​

9%​

8%​

7%​

6%​

6%​

5%​

5%​

4%​

4%​

4%​

4%​

DE

133%​

103%​

99%​

69%​

62%​

48%​

38%​

31%​

25%​

21%​

19%​

17%​

16%​

13%​

11%​

11%​

11%​

11%​

10%​

10%​

8%​

9%​

9%​

7%​

EF

133%​

113%​

101%​

70%​

57%​

47%​

37%​

30%​

22%​

17%​

16%​

14%​

13%​

11%​

9%​

9%​

9%​

9%​

8%​

9%​

7%​

8%​

8%​

6%​

GH

133%​

114%​

102%​

64%​

53%​

44%​

36%​

31%​

26%​

23%​

21%​

18%​

13%​

10%​

8%​

7%​

6%​

6%​

5%​

5%​

4%​

4%​

3%​

3%​

IJ

133%​

111%​

102%​

67%​

56%​

45%​

36%​

29%​

21%​

17%​

15%​

14%​

13%​

10%​

9%​

9%​

9%​

9%​

8%​

8%​

7%​

7%​

7%​

5%​

JK

133%​

95%​

86%​

61%​

51%​

41%​

32%​

26%​

19%​

15%​

14%​

12%​

11%​

9%​

8%​

8%​

8%​

8%​

7%​

7%​

6%​

7%​

7%​

5%​

LM

133%​

112%​

104%​

73%​

61%​

49%​

39%​

32%​

22%​

18%​

17%​

15%​

14%​

11%​

10%​

9%​

9%​

9%​

8%​

9%​

7%​

8%​

8%​

6%​

NO

133%​

108%​

96%​

62%​

51%​

43%​

35%​

30%​

25%​

22%​

20%​

17%​

13%​

10%​

8%​

7%​

6%​

6%​

6%​

5%​

5%​

4%​

4%​

4%​

PQ

133%​

113%​

104%​

67%​

56%​

42%​

33%​

27%​

26%​

21%​

19%​

18%​

16%​

11%​

10%​

8%​

7%​

7%​

7%​

7%​

5%​

6%​

6%​

6%​

RS

133%​

113%​

100%​

62%​

49%​

46%​

38%​

29%​

27%​

23%​

20%​

18%​

14%​

14%​

12%​

12%​

11%​

11%​

10%​

9%​

8%​

7%​

7%​

7%​

TU

133%​

116%​

96%​

68%​

54%​

50%​

42%​

32%​

29%​

25%​

22%​

19%​

16%​

15%​

14%​

13%​

12%​

12%​

11%​

10%​

9%​

8%​

8%​

7%​

VW

133%​

115%​

103%​

64%​

50%​

47%​

39%​

30%​

28%​

23%​

21%​

18%​

15%​

14%​

13%​

12%​

12%​

11%​

10%​

9%​

8%​

8%​

7%​

7%​

XY

100%​

84%​

75%​

69%​

56%​

52%​

43%​

35%​

25%​

22%​

14%​

10%​

10%​

10%​

8%​

8%​

7%​

6%​

5%​

4%​

3%​

2%​

1%​

1%​

ZA

133%​

116%​

102%​

62%​

50%​

39%​

32%​

25%​

23%​

19%​

17%​

15%​

9%​

7%​

5%​

4%​

2%​

2%​

2%​

2%​

2%​

2%​

2%​

2%​

ZB

133%​

110%​

96%​

60%​

49%​

39%​

33%​

27%​

22%​

19%​

16%​

14%​

13%​

12%​

11%​

10%​

9%​

9%​

6%​

4%​

4%​

4%​

4%​

4%​

ZC

133%​

119%​

106%​

72%​

65%​

54%​

44%​

38%​

33%​

30%​

25%​

23%​

19%​

13%​

9%​

8%​

7%​

7%​

5%​

3%​

3%​

3%​

3%​

3%​

ZD

133%​

112%​

97%​

62%​

50%​

42%​

32%​

29%​

24%​

22%​

18%​

16%​

12%​

9%​

6%​

5%​

5%​

5%​

3%​

2%​

2%​

2%​

2%​

2%​

ZE

133%​

108%​

99%​

63%​

54%​

41%​

34%​

25%​

20%​

16%​

15%​

13%​

11%​

11%​

9%​

9%​

9%​

7%​

5%​

6%​

5%​

3%​

2%​

2%​

ZF

133%​

114%​

102%​

66%​

58%​

48%​

37%​

31%​

23%​

19%​

14%​

11%​

9%​

10%​

9%​

6%​

3%​

2%​

3%​

1%​

3%​

3%​

3%​

3%​

ZG

133%​

101%​

94%​

61%​

49%​

42%​

35%​

31%​

26%​

23%​

19%​

15%​

13%​

11%​

9%​

6%​

7%​

6%​

4%​

2%​

1%​

2%​

1%​

2%​

ZH

133%​

113%​

103%​

67%​

54%​

47%​

38%​

34%​

29%​

25%​

21%​

16%​

14%​

12%​

9%​

7%​

8%​

7%​

4%​

2%​

1%​

2%​

1%​

3%​

ZI

133%​

117%​

104%​

69%​

55%​

52%​

44%​

34%​

32%​

28%​

25%​

22%​

19%​

18%​

17%​

16%​

16%​

15%​

14%​

13%​

12%​

12%​

11%​

11%​

ZK

133%​

124%​

110%​

68%​

54%​

51%​

42%​

32%​

30%​

25%​

22%​

19%​

16%​

15%​

14%​

13%​

12%​

12%​

11%​

10%​

9%​

8%​

8%​

7%​

ZL

133%​

109%​

96%​

60%​

47%​

44%​

37%​

28%​

26%​

22%​

19%​

17%​

14%​

13%​

12%​

11%​

11%​

10%​

9%​

9%​

8%​

7%​

7%​

7%​

ZM

133%​

102%​

91%​

56%​

45%​

42%​

35%​

27%​

25%​

21%​

18%​

16%​

13%​

12%​

11%​

11%​

10%​

10%​

9%​

8%​

7%​

7%​

6%​

6%​

ZN

133%​

92%​

81%​

51%​

40%​

37%​

31%​

24%​

22%​

18%​

16%​

14%​

12%​

11%​

10%​

10%​

9%​

9%​

8%​

7%​

6%​

6%​

6%​

6%​

Number	Curve	Output
0.00​	PQ
0.00​	RS
0.00​	TU
0.00​	VW
0.00​	XY
58.58​	ZA
31.70​	ZB
25.21​	ZC
44.23​	ZD
31.91​	ZE
23.68​	ZF
38.67​	ZG
16.54​	ZH
29.79​	ZI
37.78​	ZK
41.75​	ZL
33.38​	ZM
32.92​	ZN
72.87​	AB
58.81​	BC
84.17​	CD
96.53​	DE
121.68​	EF
96.47​	GH
127.91​	IJ
162.97​	JK
154.86​	LM
105.88​	NO
123.19​	PQ
129.65​	RS
141.00​	TU
169.10​	VW
166.30​	XY
120.73​	ZA
153.66​	ZB
141.23​	ZC
160.52​	ZD
133.15​	ZE
125.28​	ZF
110.03​	ZG
108.36​	ZH
159.77​	ZI
138.81​	ZK
152.85​	ZL
159.16​	ZM
188.25​	ZN
151.74​	AB
145.25​	BC
152.52​	CD
195.17​	DE
140.51​	EF
172.04​	GH
138.66​	IJ
205.03​	JK
192.77​	LM
201.36​	NO
204.44​	PQ
269.43​	RS
212.78​	TU
216.02​	VW
255.27​	XY
218.41​	ZA
215.95​	ZB
202.22​	ZC
204.31​	ZD
198.54​	ZE
225.21​	ZF
250.65​	ZG
251.08​	ZH
232.51​	ZI
245.50​	ZK
232.29​	ZL
243.87​	ZM
249.74​	ZN
258.12​	AB
264.19​	BC
271.38​	CD
278.19​	DE
285.30​	EF
289.59​	GH
293.59​	IJ
296.28​	JK
298.92​	LM
301.21​	NO
303.79​	PQ
307.35​	RS
310.86​	TU
314.51​	VW
318.13​	XY
318.13​	ZA
316.98​	ZB
315.83​	ZC
314.67​	ZD
311.12​	ZE
307.79​	ZF
304.95​	ZG
302.51​	ZH
300.36​	ZI
298.45​	ZK
296.71​	ZL
295.12​	ZM
293.64​	ZN
292.24​	AB
287.36​	BC
282.53​	CD
277.74​	DE
272.99​	EF
268.26​	GH
263.55​	IJ
258.85​	JK
254.17​	LM
249.49​	NO
242.06​	PQ
241.79​	RS
234.11​	TU
209.11​	VW
202.04​	XY
196.31​	ZA
189.00​	ZB
181.73​	ZC
174.50​	ZD
167.30​	ZE
160.12​	ZF
152.97​	ZG
143.59​	ZH
138.73​	ZI
130.41​	ZK
122.78​	ZL
115.78​	ZM
110.40​	ZN
105.53​	AB
101.12​	BC
97.14​	CD
93.53​	DE
90.26​	EF
87.31​	GH
82.88​	IJ
78.83​	JK
75.41​	LM
72.51​	NO
70.05​	PQ
67.98​	RS
66.22​	TU
64.73​	VW
63.47​	XY
62.41​	ZA
61.50​	ZB
60.74​	ZC
60.74​	ZD

for this I have edited the vba like this

Sub CalculateDiagonalSumNew()
    Dim ws As Worksheet
    Dim Result As Double
    Dim lastrow As Long, i As Long, j As Long, k As Long
    Dim CurveArr As Variant, OutputArr As Variant
    Dim CurveName As String
    Dim CurveRow As Long
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Get the last row
    lastrow = 184
    
    ws.Range("C36:C" & lastrow).ClearContents
    
    
    ' Load curve library and output data into arrays
    CurveArr = ws.Range("C5:AA32").Value
    OutputArr = ws.Range("A36:C" & lastrow).Value
    
    
    For i = LBound(OutputArr, 1) To UBound(OutputArr, 1)
        CurveName = OutputArr(i, 2)
        CurveRow = 0
        
        ' Match the curve name with the curve library row
        For j = 1 To UBound(CurveArr, 1)
            If ws.Cells(4 + j, 2).Value = CurveName Then
                CurveRow = j
                Exit For
            End If
        Next j
        
        ' If error
        
        If CurveRow = 0 Then
            MsgBox "Error: Curve name '" & CurveName & "' not found in curve library."
            Exit Sub
        End If
        
        For j = 3 To 3
            OutputArr(i, j) = OutputArr(i, 1) * CurveArr(CurveRow, j - 2)
        Next j
    Next i
    
    ' Calculate the diagonal sum
    For i = UBound(OutputArr, 1) To LBound(OutputArr, 1) Step -1
        k = 0
        For j = 3 To i + 2
            OutputArr(i, UBound(OutputArr, 2)) = OutputArr(i, UBound(OutputArr, 2)) + OutputArr(i - k, j)
            k = k + 1
        Next j
    Next
        
    ' Output
    For i = LBound(OutputArr, 1) To UBound(OutputArr, 1)
        ws.Cells(i + 9, 6).Value = OutputArr(i, UBound(OutputArr, 2))
    Next i
    
End Sub

I know the table is too long to be pasted here in actual . Apologies for that.
Can you help one more time please?

 

[myall_blues]

But the moment I try to change the range of output to c column it messes up the things.

Can you clarify what you mean by this, specifically the 'output to c column'?

Also if it's possible for you to download and use XL2BB to post data and curve array that would make life a lot easier.

 

[myall_blues]

Hi
I've been working on this with some success with your initial test data, but I've come up against a problem when I move to the full dataset that needs some input from yourself.
Let me see if I can explain it for you.
Your test dataset is a square array - i.e. there are three columns in the curve array and three rows in the data array, so the output column calculates for n=1,2,3 and all is well.
For your full dataset, there are 24 columns and 149 rows. For the sake of demonstration I've scaled that back to 3 columns (24/8) and 18 rows (approx 149/8) in the dataset below, but the number of rows is irrelevant here as long as it is greater than the number of columns.
As you can see I can calculate the first total as just R1C1, the second as R2C1+R1C2, and the third as R3C1+R2C2+R1C3 but then what? How should the total be calculated for the fourth row onward?

diagonalsum.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4		Curve library	M1	M2	M3		Output
5		AB	1.31	1.24	1.16		58.58	ZA	76.7398	72.6392	67.9528	76.7398
6		BC	1.33	1.17	1.04		31.7	ZB	42.161	37.089	32.968	114.8002
7		CD	1.33	1.07	0.95		25.21	ZC	33.5293	26.9747	23.9495	138.5711
8		DE	1.33	1.03	0.99		44.23	ZD	58.8259	45.5569	43.7877	??
9		EF	1.33	1.13	1.01		31.91	ZE	42.4403	36.0583	32.2291
10		GH	1.33	1.14	1.02		23.68	ZF	31.4944	26.9952	24.1536
11		IJ	1.33	1.11	1.02		38.67	ZG	51.4311	42.9237	39.4434
12		JK	1.33	0.95	0.86		16.54	ZH	21.9982	15.713	14.2244
13		LM	1.33	1.12	1.04		29.79	ZI	39.6207	33.3648	30.9816
14		NO	1.33	1.08	0.96		37.78	ZK	50.2474	40.8024	36.2688
15		PQ	1.33	1.13	1.04		41.75	ZL	55.5275	47.1775	43.42
16		RS	1.33	1.13	1		33.38	ZM	44.3954	37.7194	33.38
17		TU	1.33	1.16	0.96		32.92	ZN	43.7836	38.1872	31.6032
18		VW	1.33	1.15	1.03		72.87	AB	96.9171	83.8005	75.0561
19		XY	1	0.84	0.75		58.81	BC	58.81	49.4004	44.1075
20		ZA	1.33	1.16	1.02		84.17	CD	111.9461	97.6372	85.8534
21		ZB	1.33	1.1	0.96		96.53	DE	128.3849	106.183	92.6688
22		ZC	1.33	1.19	1.06		121.68	EF	161.8344	144.7992	128.9808
23		ZD	1.33	1.12	0.97		96.47	GH	128.3051	108.0464	93.5759
24
Sheet3

 

[spgexcel]

Hi,

Apologies for the scattered data I provided. I am posting the XL2BB version of data here.
Here is what I am trying to do.
My actual model has curve library of 24 months long. It could stretch up to 60 months as well.
By c column as output I mean the vba will paste the output in c column. earlier the code pasted the same in column F.
Also, 3 x 3 grid in earlier was just an example as I thought i would extend the code to larger pool of the table.

As you can see in cells starting from D36:AA55 I have pulled the respective curve using index match from curve library and multiplied by the respective number in column A.
For example: row 36: D36 onwards the formula is =INDEX(D$5:D$32,MATCH($B36,$C$5:$C$32,0),1)*$A36
So this creates a grid form which I am doing diagonal sum. For ease, I have color coded the same till 41 row. It should keep on extending as each row progresses.
In the example, my row ends at 55 but in my model the number rows are more than 100. I want to do away the grid portion. and let th vba do the job of grid + diagonal sum.

Trial.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
4	Curve Name	M1	M2	M3	M4	M5	M6	M7	M8	M9	M10	M11	M12	M13	M14	M15	M16	M17	M18	M19	M20	M21	M22	M23	M24
5	AB	131%	124%	116%	75%	60%	40%	30%	24%	20%	17%	17%	15%	13%	13%	12%	12%	12%	11%	10%	9%	8%	8%	8%	8%
6	BC	133%	117%	104%	65%	51%	48%	40%	30%	28%	24%	21%	18%	15%	14%	13%	12%	12%	11%	10%	9%	8%	8%	7%	7%
7	CD	133%	107%	95%	61%	51%	42%	35%	29%	25%	22%	20%	17%	13%	9%	8%	7%	6%	6%	5%	5%	4%	4%	4%	4%
8	DE	133%	103%	99%	69%	62%	48%	38%	31%	25%	21%	19%	17%	16%	13%	11%	11%	11%	11%	10%	10%	8%	9%	9%	7%
9	EF	133%	113%	101%	70%	57%	47%	37%	30%	22%	17%	16%	14%	13%	11%	9%	9%	9%	9%	8%	9%	7%	8%	8%	6%
10	GH	133%	114%	102%	64%	53%	44%	36%	31%	26%	23%	21%	18%	13%	10%	8%	7%	6%	6%	5%	5%	4%	4%	3%	3%
11	IJ	133%	111%	102%	67%	56%	45%	36%	29%	21%	17%	15%	14%	13%	10%	9%	9%	9%	9%	8%	8%	7%	7%	7%	5%
12	JK	133%	95%	86%	61%	51%	41%	32%	26%	19%	15%	14%	12%	11%	9%	8%	8%	8%	8%	7%	7%	6%	7%	7%	5%
13	LM	133%	112%	104%	73%	61%	49%	39%	32%	22%	18%	17%	15%	14%	11%	10%	9%	9%	9%	8%	9%	7%	8%	8%	6%
14	NO	133%	108%	96%	62%	51%	43%	35%	30%	25%	22%	20%	17%	13%	10%	8%	7%	6%	6%	6%	5%	5%	4%	4%	4%
15	PQ	133%	113%	104%	67%	56%	42%	33%	27%	26%	21%	19%	18%	16%	11%	10%	8%	7%	7%	7%	7%	5%	6%	6%	6%
16	RS	133%	113%	100%	62%	49%	46%	38%	29%	27%	23%	20%	18%	14%	14%	12%	12%	11%	11%	10%	9%	8%	7%	7%	7%
17	TU	133%	116%	96%	68%	54%	50%	42%	32%	29%	25%	22%	19%	16%	15%	14%	13%	12%	12%	11%	10%	9%	8%	8%	7%
18	VW	133%	115%	103%	64%	50%	47%	39%	30%	28%	23%	21%	18%	15%	14%	13%	12%	12%	11%	10%	9%	8%	8%	7%	7%
19	XY	100%	84%	75%	69%	56%	52%	43%	35%	25%	22%	14%	10%	10%	10%	8%	8%	7%	6%	5%	4%	3%	2%	1%	1%
20	ZA	133%	116%	102%	62%	50%	39%	32%	25%	23%	19%	17%	15%	9%	7%	5%	4%	2%	2%	2%	2%	2%	2%	2%	2%
21	ZB	133%	110%	96%	60%	49%	39%	33%	27%	22%	19%	16%	14%	13%	12%	11%	10%	9%	9%	6%	4%	4%	4%	4%	4%
22	ZC	133%	119%	106%	72%	65%	54%	44%	38%	33%	30%	25%	23%	19%	13%	9%	8%	7%	7%	5%	3%	3%	3%	3%	3%
23	ZD	133%	112%	97%	62%	50%	42%	32%	29%	24%	22%	18%	16%	12%	9%	6%	5%	5%	5%	3%	2%	2%	2%	2%	2%
24	ZE	133%	108%	99%	63%	54%	41%	34%	25%	20%	16%	15%	13%	11%	11%	9%	9%	9%	7%	5%	6%	5%	3%	2%	2%
25	ZF	133%	114%	102%	66%	58%	48%	37%	31%	23%	19%	14%	11%	9%	10%	9%	6%	3%	2%	3%	1%	3%	3%	3%	3%
26	ZG	133%	101%	94%	61%	49%	42%	35%	31%	26%	23%	19%	15%	13%	11%	9%	6%	7%	6%	4%	2%	1%	2%	1%	2%
27	ZH	133%	113%	103%	67%	54%	47%	38%	34%	29%	25%	21%	16%	14%	12%	9%	7%	8%	7%	4%	2%	1%	2%	1%	3%
28	ZI	133%	117%	104%	69%	55%	52%	44%	34%	32%	28%	25%	22%	19%	18%	17%	16%	16%	15%	14%	13%	12%	12%	11%	11%
29	ZK	133%	124%	110%	68%	54%	51%	42%	32%	30%	25%	22%	19%	16%	15%	14%	13%	12%	12%	11%	10%	9%	8%	8%	7%
30	ZL	133%	109%	96%	60%	47%	44%	37%	28%	26%	22%	19%	17%	14%	13%	12%	11%	11%	10%	9%	9%	8%	7%	7%	7%
31	ZM	133%	102%	91%	56%	45%	42%	35%	27%	25%	21%	18%	16%	13%	12%	11%	11%	10%	10%	9%	8%	7%	7%	6%	6%
32	ZN	133%	92%	81%	51%	40%	37%	31%	24%	22%	18%	16%	14%	12%	11%	10%	10%	9%	9%	8%	7%	6%	6%	6%	6%
Sheet1

 

[spgexcel]

It would not let me paste both tables in one message.
Here is the grid table I mentioned above.

Trial.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
35	Number	Curve	Output	M1	M2	M3	M4	M5	M6	M7	M8	M9	M10	M11	M12	M13	M14	M15	M16	M17	M18	M19	M20	M21	M22	M23	M24
36	58.58	ZA	78.1060261	78.1	67.8	59.5	36	29	23	19	15	13	11	10	8.7	5.5	3.8	2.9	2.2	1.4	1.2	1.4	1	1	1	1.2	1.2
37	31.70	ZB	110.030656	42.3	35	30.4	19	16	12	10	8.5	7	6.1	4.9	4.5	4	3.7	3.4	3.2	2.8	2.8	2	1.3	1.2	1.4	1.2	1.2
38	25.21	ZC	128.145983	33.6	30.1	26.7	18	16	14	11	9.6	8.4	7.6	6.2	5.8	4.7	3.3	2.4	2	1.8	1.8	1.3	0.8	0.8	0.9	0.8	0.8
39	44.23	ZD	155.696382	59	49.4	43	27	22	19	14	13	11	9.6	8.1	7.1	5.5	3.8	2.8	2.4	2.1	2.1	1.5	1	0.9	1	0.9	0.9
40	31.91	ZE	166.935453	42.6	34.6	31.6	20	17	13	11	8	6.4	5.2	4.7	4.1	3.5	3.5	2.9	2.9	2.8	2.1	1.7	1.9	1.5	1.1	0.6	0.6
41	23.68	ZF	165.493904	31.6	27	24.2	16	14	11	8.7	7.4	5.4	4.6	3.4	2.7	2.1	2.4	2.1	1.4	0.7	0.5	0.6	0.3	0.6	0.6	0.6	0.6
42	38.67	ZG		51.6	38.9	36.3	24	19	16	14	12	10	8.8	7.2	5.8	4.9	4.2	3.3	2.4	2.8	2.4	1.5	0.6	0.5	0.6	0.5	1
43	16.54	ZH		22	18.7	17.1	11	8.9	7.7	6.3	5.6	4.7	4.1	3.4	2.7	2.3	2	1.6	1.1	1.3	1.1	0.7	0.3	0.2	0.3	0.2	0.4
44	29.79	ZI		39.7	34.9	30.9	20	16	15	13	10	9.6	8.2	7.4	6.6	5.6	5.4	5	4.9	4.7	4.5	4.2	3.9	3.6	3.5	3.4	3.3
45	37.78	ZK		50.4	46.8	41.4	26	20	19	16	12	11	9.4	8.3	7.3	5.9	5.6	5.1	4.9	4.7	4.5	4.1	3.7	3.3	3.1	2.9	2.8
46	41.75	ZL		55.7	45.5	40.2	25	20	19	15	12	11	9.1	8.1	7.1	5.8	5.5	5	4.8	4.6	4.3	3.9	3.6	3.2	3	2.8	2.7
47	33.38	ZM		44.5	34.2	30.2	19	15	14	12	8.9	8.2	6.9	6.1	5.3	4.3	4.1	3.8	3.6	3.4	3.3	3	2.7	2.4	2.2	2.1	2.1
48	32.92	ZN		43.9	30.2	26.7	17	13	12	10	7.8	7.2	6.1	5.4	4.7	3.8	3.6	3.3	3.2	3	2.9	2.6	2.4	2.1	2	1.9	1.8
49	72.87	AB		95.8	90.2	84.2	55	44	29	22	17	15	13	12	11	9.7	9.6	8.8	8.4	8.4	8	7.3	6.6	5.8	5.5	5.5	5.6
50	58.81	BC		78.4	69	61	38	30	28	23	18	17	14	12	11	8.7	8.3	7.6	7.2	6.9	6.6	6	5.4	4.8	4.5	4.3	4.1
51	84.17	CD		112	89.8	79.8	51	43	36	29	25	21	18	17	14	11	7.9	6.8	6	5.4	5	4.6	4.3	3.8	3.6	3.2	3.1
52	96.53	DE		129	99.9	95.1	66	60	47	37	30	25	20	18	16	15	12	11	10	10	10	9.3	9.9	8.1	8.8	8.9	6.4
53	121.68	EF		162	138	123	85	70	58	45	37	26	21	19	17	16	13	11	11	11	11	9.9	11	8.7	9.3	9.5	6.8
54	96.47	GH		129	110	98.2	62	51	42	35	29	25	22	20	17	13	9.4	8	7	6.2	5.7	5.2	4.6	4.1	3.9	3.3	3.1
55	127.91	IJ		171	142	131	86	72	58	46	37	26	21	19	18	16	13	11	11	11	11	10	11	8.7	9.4	9.6	6.8
Sheet1

Cell Formulas
Range	Formula
D36:AA55	D36	=INDEX(D$5:D$32,MATCH($B36,$C$5:$C$32,0),1)*$A36
C36	C36	=D36
C37	C37	=D37+E36
C38	C38	=D38+E37+F36
C39	C39	=D39+E38+F37+G36
C40	C40	=D40+E39+F38+G37+H36
C41	C41	=D41+E40+F39+G38+H37+I36

 

[myall_blues]

OK try this code. Your most recent data upload and this code gives these results. Also confirmed with original 3x3 data set.

diagonalsum.xlsm
	A	B	C
65	58.57952	ZA	78.10603
66	31.70395	ZB	110.0307
67	25.20828	ZC	128.146
68	44.22953	ZD	155.6964
69	31.91293	ZE	166.9355
70	23.67512	ZF	165.4939
71	38.66976	ZG	184.9713
72	16.53599	ZH	166.1203
73	29.78681	ZI	179.0532
74	37.77586	ZK	195.0572
75	41.74604	ZL	222.7101
76	33.38444	ZM	225.6996
77	32.9209	ZN	221.2691
78	72.86617	AB	267.7836
79	58.80931	BC	309.4164
80	84.16651	CD	377.0919
81	96.52769	DE	426.6578
82	121.6822	EF	502.3579
83	96.47499	GH	540.5092
84	127.9136	IJ	621.2475
85	96.52769	DE	641.0556
86	121.6822	EF	675.1928
87	96.47499	GH	685.0759
88	127.9136	IJ	742.1439
Sheet2

Sub CalculateDiagonalSum()
    Dim ws As Worksheet
    Dim Result As Double
    Dim lastrow As Long, i As Long, j As Long, k As Long
    Dim maxCurves As Long, maxOutput As Long, maxArrays As Long
    Dim CurveName As String
    Dim CurveArr, OutputArr
    '
    ' Set worksheet
    '
    Set ws = ThisWorkbook.Sheets("Sheet2")
    '
    ' Get last row
    '
    lastrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    '
    ' Load curve library and data into arrays
    '
    CurveArr = ws.Range("C5:aa32")
    ws.Range("c35:c" & lastrow).ClearContents
    OutputArr = ws.Range("a36:b" & lastrow)
    '
    ' Get maximum dimensions of arrays - the smaller of which becomes the
    ' maximum possible diagonal sum that can be calculated.
    '
    maxCurves = UBound(CurveArr, 2) - 1
    maxOutput = UBound(OutputArr, 1)
    maxArrays = maxCurves
    If maxOutput < maxCurves Then
        maxArrays = maxOutput
    End If
    '
    ' Redim the output array to accomodate the intermediate calculations
    ' Redim the curve array to get same number of columns as output array
    ' (minus output column)
    '
    ReDim Preserve OutputArr(1 To UBound(OutputArr, 1), 1 To UBound(CurveArr, 2) + 2)
    ReDim Preserve CurveArr(1 To UBound(CurveArr, 1), 1 To UBound(CurveArr, 2) + 2)
    For i = LBound(CurveArr, 1) To UBound(CurveArr, 1)
        For j = UBound(CurveArr, 2) - 1 To LBound(CurveArr, 2) Step -1
            CurveArr(i, j + 1) = CurveArr(i, j)
        Next j
    Next i '
    ' For each row of the output array
    '
    For i = LBound(OutputArr, 1) To UBound(OutputArr, 1)
        CurveName = OutputArr(i, 2)
        '
        ' For each row of the curve array
        '
        For j = LBound(CurveArr, 1) To UBound(CurveArr, 1)
            '
            ' If the curvename is found multiply by the array values
            '
            If CurveName = CurveArr(j, 1) Then
                For k = LBound(CurveArr, 2) + 2 To UBound(CurveArr, 2)
                    OutputArr(i, k) = OutputArr(i, 1) * CurveArr(j, k)
                Next k
            End If
        Next j
    Next i
    '
    ' Work out the diagonal sums
    '
    For i = maxArrays To LBound(OutputArr, 1) Step -1
        k = 0
        For j = LBound(OutputArr, 2) + 2 To i + 2
            OutputArr(i, UBound(OutputArr, 2)) = OutputArr(i, UBound(OutputArr, 2)) + _
            OutputArr(i - k, j)
            k = k + 1
        Next j
    Next
    '
    ' Output to desired location
    ' First write reference data
    '
    Range("A65").Resize(UBound(OutputArr, 1), 2) = OutputArr
    '
    ' Then write last array column
    '
    For i = LBound(OutputArr, 1) To UBound(OutputArr, 1)
        Cells(i + 64, 3).Value = OutputArr(i, UBound(OutputArr, 2))
    Next i
End Sub