How to make excel function to output array

Question

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?

Use, BYROW so the dist function only sees one value at a time? — Scott Craner
– Scott Craner, Commented Jan 9, 2023 at 22:18
i just know there is a way to do this, but BYROW only limits my lambda to one variable, when I need at least the "val" and "eq" values as parameters — cheezit97
– cheezit97, Commented Jan 10, 2023 at 2:45
Thank you for introducing BYROW to me. However, it doesn't fully solve my problem. I get that it goes row by row, applying a lambda to each row. However, I only get one parameter this way. I cannot specifically say "column B of this row is the equation, and column D is the value, plug the value in!" — cheezit97
– cheezit97, Commented Jan 10, 2023 at 3:00
I FIGURED IT OUT! The MAP function is basically BYROW, except you can add more columns to the input. I got exactly what I needed. Thanks for leading me on the right trail — cheezit97
– cheezit97, Commented Jan 10, 2023 at 3:10
I was about to suggest to use MAP but you figured it out. I cannot test it because I use Excel web and you need Name Manager for that. I am glad it worked — David Leal
– David Leal, Commented Jan 10, 2023 at 3:26

cheezit97 · Accepted Answer · 2023-01-10 21:44:31Z

1

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...

answered Jan 10, 2023 at 21:44

cheezit97

3151 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make excel function to output array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related