Matrix returns wrong values

Margooox · Nov 27, 2023

Hello everyone!

I'm trying to create exponential damage curves for a set of materials by basing myself off of this data:

Damage amount (Y axis)	0	25	50	75	100
Light Leather	30.0	21.0	15.0	7.5	3.0
Heavy Leather	40.0	32.0	20.0	12.0	6.0
Steel	50.0	42.5	35.0	20.0	12.5
Carbon Steel	50.0	45.0	42.5	32.5	17.5
Hardened Steel	40.0	38.0	34.0	20.0	12.0
Ceramics	40.0	32.0	20.0	16.0	4.0
Polyethylene	20.0	19.0	17.0	6.0	2.0
Titanium	70.0	66.5	63.0	56.0	42.0
Kevlar	70.0	69.3	66.5	52.5	42.0
Aramid	70.0	63.0	59.5	49.0	35.0
Boron Carbide	60.0	45.0	30.0	27.0	24.0
Iron	30.0	25.5	18.0	12.0	4.5

I then use this formula to create a matrix that would show me the results from all points in the curve (from 0 to 100):

Excel Formula:

=BYCOL(TRANSPOSE(H3:H14),LAMBDA(X,let(n,SEQUENCE(101,1,0,1),list,filter(I3:M,H3:H=x),{X;GROWTH(list,I2:M2,N6:N106)})))

H3:H14 being the range of the material list (leather, steel etc...), I2:M being my amount of damage (Y axis), I3:M14 being my X axis.

This then generates the following matrix:


	Light Leather	Heavy Leather	Steel	Carbon Steel	Hardened Steel	Ceramics	Polyethylene	Titanium	Kevlar	Aramid	Boron Carbide	Iron
0.0	35.9	46.5	57.6	57.2	48.3	50.5	30.1	74.4	76.4	74.6	55.9	37.1
1.0	35.1	45.7	56.8	56.7	47.7	49.4	29.4	74.0	76.0	74.1	55.4	36.4
2.0	34.3	44.8	56.0	56.1	47.1	48.4	28.7	73.7	75.6	73.7	54.9	35.8
3.0	33.5	44.0	55.2	55.6	46.5	47.4	28.1	73.3	75.2	73.2	54.4	35.1
4.0	32.8	43.1	54.4	55.1	46.0	46.4	27.4	73.0	74.9	72.7	53.9	34.5
5.0	32.1	42.3	53.6	54.5	45.4	45.4	26.8	72.6	74.5	72.2	53.4	33.9
6.0	31.3	41.5	52.9	54.0	44.9	44.4	26.2	72.3	74.1	71.7	52.9	33.3
7.0	30.7	40.7	52.2	53.5	44.3	43.5	25.6	71.9	73.7	71.3	52.4	32.7
8.0	30.0	39.9	51.4	53.0	43.8	42.6	25.0	71.6	73.3	70.8	51.9	32.1
9.0	29.3	39.2	50.7	52.5	43.2	41.7	24.4	71.2	72.9	70.4	51.4	31.5
10.0	28.6	38.5	50.0	52.0	42.7	40.8	23.9	70.9	72.6	69.9	50.9	30.9
11.0	28.0	37.7	49.3	51.5	42.2	40.0	23.3	70.6	72.2	69.4	50.4	30.4
12.0	27.4	37.0	48.6	51.0	41.7	39.1	22.8	70.2	71.8	69.0	50.0	29.8
13.0	26.8	36.3	47.9	50.5	41.2	38.3	22.3	69.9	71.4	68.5	49.5	29.3
14.0	26.2	35.6	47.3	50.0	40.7	37.5	21.8	69.6	71.1	68.1	49.0	28.8
15.0	25.6	34.9	46.6	49.5	40.2	36.7	21.3	69.2	70.7	67.6	48.6	28.2
16.0	25.0	34.3	45.9	49.0	39.7	36.0	20.8	68.9	70.3	67.2	48.1	27.7
17.0	24.5	33.6	45.3	48.5	39.2	35.2	20.3	68.6	70.0	66.8	47.7	27.2
18.0	23.9	33.0	44.7	48.1	38.7	34.5	19.9	68.2	69.6	66.3	47.2	26.7
19.0	23.4	32.4	44.0	47.6	38.3	33.7	19.4	67.9	69.2	65.9	46.8	26.3
20.0	22.9	31.8	43.4	47.2	37.8	33.0	19.0	67.6	68.9	65.5	46.4	25.8
21.0	22.4	31.2	42.8	46.7	37.4	32.3	18.5	67.3	68.5	65.0	45.9	25.3
22.0	21.9	30.6	42.2	46.2	36.9	31.7	18.1	67.0	68.2	64.6	45.5	24.9
23.0	21.4	30.0	41.6	45.8	36.5	31.0	17.7	66.6	67.8	64.2	45.1	24.4
24.0	20.9	29.4	41.0	45.4	36.0	30.4	17.3	66.3	67.5	63.8	44.7	24.0
25.0	20.4	28.9	40.5	44.9	35.6	29.7	16.9	66.0	67.1	63.4	44.2	23.5
26.0	20.0	28.3	39.9	44.5	35.1	29.1	16.5	65.7	66.8	62.9	43.8	23.1
27.0	19.5	27.8	39.3	44.1	34.7	28.5	16.1	65.4	66.4	62.5	43.4	22.7
28.0	19.1	27.3	38.8	43.6	34.3	27.9	15.8	65.1	66.1	62.1	43.0	22.3
29.0	18.7	26.7	38.2	43.2	33.9	27.3	15.4	64.8	65.7	61.7	42.6	21.9
30.0	18.3	26.2	37.7	42.8	33.5	26.7	15.1	64.4	65.4	61.3	42.2	21.5
31.0	17.8	25.7	37.2	42.4	33.1	26.2	14.7	64.1	65.1	60.9	41.8	21.1
32.0	17.4	25.3	36.7	42.0	32.7	25.6	14.4	63.8	64.7	60.5	41.4	20.7
33.0	17.1	24.8	36.1	41.6	32.3	25.1	14.1	63.5	64.4	60.1	41.0	20.4
34.0	16.7	24.3	35.6	41.2	31.9	24.6	13.7	63.2	64.1	59.7	40.7	20.0
35.0	16.3	23.9	35.1	40.8	31.5	24.0	13.4	62.9	63.7	59.3	40.3	19.6
36.0	15.9	23.4	34.6	40.4	31.1	23.5	13.1	62.6	63.4	58.9	39.9	19.3
37.0	15.6	23.0	34.2	40.0	30.7	23.0	12.8	62.3	63.1	58.6	39.5	18.9
38.0	15.2	22.5	33.7	39.6	30.4	22.6	12.5	62.0	62.7	58.2	39.2	18.6
39.0	14.9	22.1	33.2	39.2	30.0	22.1	12.2	61.7	62.4	57.8	38.8	18.2
40.0	14.6	21.7	32.7	38.8	29.6	21.6	12.0	61.4	62.1	57.4	38.4	17.9
41.0	14.2	21.3	32.3	38.5	29.3	21.2	11.7	61.2	61.8	57.0	38.1	17.6
42.0	13.9	20.9	31.8	38.1	28.9	20.7	11.4	60.9	61.4	56.7	37.7	17.3
43.0	13.6	20.5	31.4	37.7	28.6	20.3	11.2	60.6	61.1	56.3	37.4	17.0
44.0	13.3	20.1	30.9	37.4	28.2	19.9	10.9	60.3	60.8	55.9	37.0	16.7
45.0	13.0	19.7	30.5	37.0	27.9	19.4	10.7	60.0	60.5	55.6	36.7	16.4
46.0	12.7	19.3	30.1	36.6	27.5	19.0	10.4	59.7	60.2	55.2	36.3	16.1
47.0	12.4	19.0	29.7	36.3	27.2	18.6	10.2	59.4	59.9	54.9	36.0	15.8
48.0	12.2	18.6	29.2	35.9	26.9	18.3	10.0	59.1	59.6	54.5	35.7	15.5
49.0	11.9	18.3	28.8	35.6	26.5	17.9	9.7	58.9	59.2	54.1	35.3	15.2
50.0	11.6	17.9	28.4	35.2	26.2	17.5	9.5	58.6	58.9	53.8	35.0	14.9
51.0	11.4	17.6	28.0	34.9	25.9	17.1	9.3	58.3	58.6	53.4	34.7	14.7
52.0	11.1	17.2	27.6	34.6	25.6	16.8	9.1	58.0	58.3	53.1	34.3	14.4
53.0	10.9	16.9	27.3	34.2	25.3	16.4	8.9	57.7	58.0	52.7	34.0	14.1
54.0	10.6	16.6	26.9	33.9	25.0	16.1	8.7	57.5	57.7	52.4	33.7	13.9
55.0	10.4	16.3	26.5	33.6	24.7	15.7	8.5	57.2	57.4	52.0	33.4	13.6
56.0	10.2	16.0	26.1	33.3	24.4	15.4	8.3	56.9	57.1	51.7	33.1	13.4
57.0	9.9	15.7	25.8	32.9	24.1	15.1	8.1	56.7	56.8	51.4	32.8	13.2
58.0	9.7	15.4	25.4	32.6	23.8	14.8	7.9	56.4	56.5	51.0	32.5	12.9
59.0	9.5	15.1	25.0	32.3	23.5	14.5	7.7	56.1	56.2	50.7	32.2	12.7
60.0	9.3	14.8	24.7	32.0	23.2	14.2	7.5	55.8	56.0	50.4	31.9	12.5
61.0	9.1	14.5	24.3	31.7	22.9	13.9	7.4	55.6	55.7	50.0	31.6	12.2
62.0	8.9	14.2	24.0	31.4	22.7	13.6	7.2	55.3	55.4	49.7	31.3	12.0
63.0	8.7	14.0	23.7	31.1	22.4	13.3	7.0	55.1	55.1	49.4	31.0	11.8
64.0	8.5	13.7	23.3	30.8	22.1	13.0	6.9	54.8	54.8	49.1	30.7	11.6
65.0	8.3	13.4	23.0	30.5	21.8	12.7	6.7	54.5	54.5	48.7	30.4	11.4
66.0	8.1	13.2	22.7	30.2	21.6	12.5	6.6	54.3	54.2	48.4	30.1	11.2
67.0	7.9	12.9	22.4	29.9	21.3	12.2	6.4	54.0	54.0	48.1	29.8	11.0
68.0	7.8	12.7	22.1	29.6	21.1	11.9	6.3	53.8	53.7	47.8	29.6	10.8
69.0	7.6	12.5	21.8	29.3	20.8	11.7	6.1	53.5	53.4	47.5	29.3	10.6
70.0	7.4	12.2	21.4	29.0	20.5	11.4	6.0	53.2	53.1	47.2	29.0	10.4
71.0	7.2	12.0	21.1	28.7	20.3	11.2	5.9	53.0	52.8	46.9	28.7	10.2
72.0	7.1	11.8	20.8	28.5	20.1	11.0	5.7	52.7	52.6	46.6	28.5	10.0
73.0	6.9	11.5	20.6	28.2	19.8	10.7	5.6	52.5	52.3	46.3	28.2	9.8
74.0	6.8	11.3	20.3	27.9	19.6	10.5	5.5	52.2	52.0	46.0	27.9	9.7
75.0	6.6	11.1	20.0	27.7	19.3	10.3	5.3	52.0	51.8	45.7	27.7	9.5
76.0	6.5	10.9	19.7	27.4	19.1	10.1	5.2	51.7	51.5	45.4	27.4	9.3
77.0	6.3	10.7	19.4	27.1	18.9	9.9	5.1	51.5	51.2	45.1	27.2	9.1
78.0	6.2	10.5	19.2	26.9	18.6	9.7	5.0	51.2	51.0	44.8	26.9	9.0
79.0	6.0	10.3	18.9	26.6	18.4	9.5	4.9	51.0	50.7	44.5	26.7	8.8
80.0	5.9	10.1	18.6	26.3	18.2	9.3	4.8	50.8	50.4	44.2	26.4	8.7
81.0	5.8	9.9	18.4	26.1	18.0	9.1	4.7	50.5	50.2	43.9	26.2	8.5
82.0	5.7	9.7	18.1	25.8	17.7	8.9	4.5	50.3	49.9	43.6	25.9	8.3
83.0	5.5	9.5	17.9	25.6	17.5	8.7	4.4	50.0	49.7	43.3	25.7	8.2
84.0	5.4	9.4	17.6	25.3	17.3	8.5	4.3	49.8	49.4	43.0	25.4	8.0
85.0	5.3	9.2	17.4	25.1	17.1	8.3	4.2	49.6	49.1	42.8	25.2	7.9
86.0	5.2	9.0	17.1	24.9	16.9	8.2	4.1	49.3	48.9	42.5	25.0	7.8
87.0	5.1	8.8	16.9	24.6	16.7	8.0	4.1	49.1	48.6	42.2	24.7	7.6
88.0	4.9	8.7	16.6	24.4	16.5	7.8	4.0	48.9	48.4	41.9	24.5	7.5
89.0	4.8	8.5	16.4	24.1	16.3	7.7	3.9	48.6	48.1	41.7	24.3	7.3
90.0	4.7	8.3	16.2	23.9	16.1	7.5	3.8	48.4	47.9	41.4	24.1	7.2
91.0	4.6	8.2	15.9	23.7	15.9	7.3	3.7	48.2	47.6	41.1	23.8	7.1
92.0	4.5	8.0	15.7	23.5	15.7	7.2	3.6	47.9	47.4	40.8	23.6	7.0
93.0	4.4	7.9	15.5	23.2	15.5	7.0	3.5	47.7	47.1	40.6	23.4	6.8
94.0	4.3	7.7	15.3	23.0	15.3	6.9	3.4	47.5	46.9	40.3	23.2	6.7
95.0	4.2	7.6	15.1	22.8	15.1	6.7	3.4	47.3	46.6	40.1	23.0	6.6
96.0	4.1	7.4	14.9	22.6	15.0	6.6	3.3	47.0	46.4	39.8	22.7	6.5
97.0	4.0	7.3	14.7	22.3	14.8	6.5	3.2	46.8	46.2	39.5	22.5	6.4
98.0	3.9	7.2	14.4	22.1	14.6	6.3	3.1	46.6	45.9	39.3	22.3	6.2
99.0	3.9	7.0	14.2	21.9	14.4	6.2	3.1	46.4	45.7	39.0	22.1	6.1
100.0	3.8	6.9	14.0	21.7	14.2	6.1	3.0	46.1	45.5	38.8	21.9	6.0

The results are all off by a little bit, Light Leather's 0 X should be 30 but in the matrix it is returned as 35.9. All the results are off in a similar way and I can't figure out why.
Any help would be highly appreciated!

Margooox · Nov 27, 2023

ranman256 · Nov 28, 2023

paste into a module,
here your matrix page is called: 'matrix'. change: Sheets("matrix").Activate ,as needed
then run: CreateMatrixNums

Code:

Private mcolQty As Collection, mcolMat As Collection

Sub CreateMatrixNums()
Dim i As Integer, j As Integer, k As Integer, m As Integer, v As Integer, x As Integer
Dim nMin As Single, nMax As Single
Dim sQty As String, sMat As String, sVal As String
Dim vList
Dim ary()

Sheets("matrix").Activate

  '=======collect qtys
Set mcolQty = New Collection
Range("B1").Select
While ActiveCell.Value <> ""
     sQty = ActiveCell.Value
     mcolQty.Add sQty, sQty
     
     ActiveCell.Offset(0, 1).Select      'next col
Wend


  '===========collect materials
Set mcolMat = New Collection

Range("A2").Select
While ActiveCell.Value <> ""
     sMat = ActiveCell.Value
     mcolMat.Add sMat, sMat
     
       'back to start cell

     ActiveCell.Offset(1, 0).Select      'next row
Wend

 'build value array
ReDim ary(mcolMat.Count, mcolQty.Count)

Dim c As Integer, r As Integer
r = 1
 'load array w mat values
Range("A2").Select
While ActiveCell.Value <> ""
     sVal = ""
     For c = 0 To mcolQty.Count
        sVal = ActiveCell.Offset(0, c).Value
        ary(r, c) = sVal
     Next
     
     r = r + 1
     ActiveCell.Offset(1, 0).Select      'next row
Wend


  '======post headers
Sheets.Add
For i = 1 To mcolMat.Count
   Range("B1").Offset(0, i - 1).Value = mcolMat(i)
Next


  '=======post values 1 thru N
Range("A2").Select
For i = 1 To mcolQty.Count
  If mcolQty(i) = 0 Then
     nMin = 0
  Else
     nMin = mcolQty(i) + 1
  End If
  
  If i = mcolQty.Count Then
    nMax = mcolQty(i)
  Else
    nMax = mcolQty(i + 1)
  End If
    'change over to new min/max
  If nMin > 0 Then nMin = nMin - 1
   
     '======post 1Mat , N values
  For x = nMin To nMax
     Cells(ActiveCell.Row, 1).Select
     ActiveCell.Value = x
     
     If x = nMax Then  'dont post max
       Beep
     Else
        For m = 1 To mcolMat.Count
           ActiveCell.Offset(0, m).Value = ary(m, i)
        Next
        ActiveCell.Offset(1, 0).Select      'next col
     End If
  Next
Next


'=====last line
   For m = 1 To mcolMat.Count
     For i = 1 To mcolQty.Count
       ActiveCell.Offset(0, m).Value = ary(m, i)
     Next
   Next
   ActiveCell.Offset(1, 0).Select      'next col

Set mcolQty = Nothing
Set mcolMat = Nothing
MsgBox "done"
End Sub

Peter_SSs · Nov 29, 2023

Margooox said:
I then use this formula ...

Excel Formula:

=BYCOL(TRANSPOSE(H3:H14),LAMBDA(X,let(n,SEQUENCE(101,1,0,1),list,filter(I3:M,H3:H=x),{X;GROWTH(list,I2:M2,N6:N106)})))

Are you sure that is your formula? It doesn't look like a valid formula to me

Matrix returns wrong values

Margooox

New Member

Excel Facts

Margooox

New Member

ranman256

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Matrix returns wrong values

Margooox

New Member

Excel Facts

Margooox

New Member

ranman256

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock