CODEWITHSUNDEEP
excelsumproduct
cheezit97
cheezit97

Reputation: 305

How to make excel function to output array

I want to use a function within sumproduct that doesn't take an array. For example, I have this dataset:

+---------+----------------------------------------------+--------+-----+
|  Topic  |                      Eq                      | Weight | Val |
+---------+----------------------------------------------+--------+-----+
| Topic 1 | LOGNORM.DIST(val,2.4,0.4, FALSE)*10.2027*D50 |     13 |   5 |
| Topic 2 | val/10                                       |     10 |   4 |
| Topic 3 | val^2                                        |      5 |   2 |
+---------+----------------------------------------------+--------+-----+

I wrote a Visual Basic module to evaluate the equations:

Public Function eval(s As String) As Variant
    eval = Evaluate(s)
End Function

And basically, I just want to be able to insert the "val" column into the equation, and then multiply against the weight and get an array of sums.

I tried this:

=SUMPRODUCT(IF(ISNUMBER(H$7:H$31),Dist(H$7:H$31,$D$7:$D$31,$E$7:$E$31))

where Dist is a lambda function:

LAMBDA(val,eq,weight,eval(CONCAT("=",SUBSTITUTE(eq,"val",val))))

But anytime I have a table longer than 1 row, it yells at me with an #N/A or #VALUE error, presumably because the LOGNORM.DIST function doesn't like having more than 1 value.

Is there a way to coerce an excel function into taking an array?

Upvotes: 2

Views: 213

Answers (1)

cheezit97
cheezit97

Reputation: 305

Thanks to the comments, I solved this with the MAP function. Posting an answer for visibility. I navigated to Formulas > Name Manager and created a new formula called Dist that does this:

=LAMBDA(val,eq,MAP(val,eq,LAMBDA(val,eq,eval(CONCAT("=",SUBSTITUTE(eq,"val",val))))))

So now, that SUMPRODUCT formula I posted basically looks like this (pseudocode):

=SUMPRODUCT(IF(ISNUMBER(ValsColumn),Dist(ValsCol,EqColumn), ...if false), WeightCol)

This will correctly input values to each of my equations with no yelling about it being an array, since I think Map goes row by row. Hopefully this makes sense to anyone in the future...

Upvotes: 1

Related Questions