Converting complex Formula into Dynamic Array

Question

Trying to create a Dynamic array from a somewhat complex formula but keep getting #N/A errors randomly.

For some reason, the y value of the equations doesn't like it.

Effectively the #N/A for the first instance is saying it can't find 0.57 in the particle diameter table on the left, despite clearly being listed.

Data to mimic:

Table1:

Particle Diameter δ (μm)	Rairborne(δ) (kg/s)	γ(δ)
0.00	0	0.000555556
0.19	2.33195E-15	0.000556338
0.38	5.3411E-15	0.000558686
0.57	7.49672E-15	0.000562599
0.76	8.93319E-15	0.000568078
0.95	9.85973E-15	0.000575121
1.14	1.04323E-14	0.00058373
1.33	1.07581E-14	0.000593905
1.52	1.09105E-14	0.000605644
1.71	1.09403E-14	0.000618949
1.90	1.0883E-14	0.000633819
2.09	1.07639E-14	0.000650254

InputParameters:

Spray Duration (minutes)
11.1

Problematic Formula:

=MAKEARRAY(10,11,LAMBDA(x,y,IF(x*7.8 <= InputParameters[[Spray Duration (minutes)]:[Spray Duration (minutes)]] * 60,

XLOOKUP(y*0.19, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[Rairborne(δ) (kg/s)]:[Rairborne(δ) (kg/s)]]) *
    (1 - EXP(-XLOOKUP(y*0.19, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) * x*7.8)) /
    XLOOKUP(y*0.19, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]),

XLOOKUP(y*0.19, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[Rairborne(δ) (kg/s)]:[Rairborne(δ) (kg/s)]]) /
    XLOOKUP(y*0.19, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) *
    (1 - EXP(-XLOOKUP(y*0.19, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) * (InputParameters[[Spray Duration (minutes)]:[Spray Duration (minutes)]] * 60))) *
    EXP(-XLOOKUP(y*0.19, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) * (x*7.8 - (InputParameters[[Spray Duration (minutes)]:[Spray Duration (minutes)]] * 60)))
)))

Correct data (When using the long formula and dragging) (particle size on top, Time down the LHS):

TIME (s)	0	0.19	0.38	0.57	0.76	0.95
0	0.00E+00	0.00E+00	0.00E+00	0.00E+00	0.00E+00	0.00E+00
7.80	0.00E+00	1.81E-14	4.16E-14	5.83E-14	6.95E-14	7.67E-14
15.60	0.00E+00	3.62E-14	8.30E-14	1.16E-13	1.39E-13	1.53E-13
23.40	0.00E+00	5.42E-14	1.24E-13	1.74E-13	2.08E-13	2.29E-13
31.20	0.00E+00	7.21E-14	1.65E-13	2.32E-13	2.76E-13	3.05E-13
39.00	0.00E+00	9.00E-14	2.06E-13	2.89E-13	3.45E-13	3.80E-13
46.80	0.00E+00	1.08E-13	2.47E-13	3.46E-13	4.13E-13	4.55E-13
54.60	0.00E+00	1.25E-13	2.87E-13	4.03E-13	4.80E-13	5.30E-13
62.40	0.00E+00	1.43E-13	3.28E-13	4.60E-13	5.48E-13	6.04E-13
70.20	0.00E+00	1.61E-13	3.68E-13	5.16E-13	6.15E-13	6.78E-13
78.00	0.00E+00	1.78E-13	4.08E-13	5.72E-13	6.82E-13	7.52E-13
85.80	0.00E+00	1.95E-13	4.47E-13	6.28E-13	7.48E-13	8.25E-13

LINK TO WORKBOOK IN IMAGE:

https://1drv.ms/x/s!AsrLaUgt0KCLxXOWIrFQWEaQoxky?e=vnORUu

Update1 Adding &"":

Update2 Adding TRUE and Setting y*0.19 to the array

You're only showing the (expected) result, not any of the data you're linking to which is necessary for us to reproduce. Please post some sample data and expected results from that (you could make use of tablesgenerator.com/markdown_tables to post a table as data) — P.b
– P.b, Commented May 8, 2024 at 21:50
Agree, Was trying to keep the question shorter. Will upload link to workbook if that's okay. — Nick
– Nick, Commented May 8, 2024 at 22:02
appreciate this isn't standard practice but for ease please see: 1drv.ms/x/s!AsrLaUgt0KCLxXFz-a8xYT9QQBbb?e=wqugjk — Nick
– Nick, Commented May 8, 2024 at 22:07
@P.b Sorry for being lazy yesterday I have re-written the question in a simpler format and provided all data and a link to replicate — Nick
– Nick, Commented May 9, 2024 at 10:42
A per latest question it seems xlookup doesn't actually function well in Makearray — Nick
– Nick, Commented May 9, 2024 at 11:34

FlexYourData · Accepted Answer · 2024-05-09 15:19:16Z

With formulas like this, it's usually best to remove redundancy so you can see what's going on more easily.

This seems to work:

=MAKEARRAY(
    10,
    11,
    LAMBDA(x, y,
        LET(
            columnLookup, ROUND(y * 0.19, 2),
            particleDiameter, ROUND(Table1[Particle Diameter δ (μm)], 2),
            sprayDuration, TAKE(InputParameters[Spray Duration (minutes)], 1, 1),
            gammaOfDelta, XLOOKUP(columnLookup, particleDiameter, Table1[γ(δ)]),
            rairBorne, XLOOKUP(columnLookup, particleDiameter, Table1[Rairborne(δ) (kg/s)]),
            IF(
                x * 7.8 <= sprayDuration * 60,
                rairBorne * (1 - EXP(-gammaOfDelta * x * 7.8)) / gammaOfDelta,
                rairBorne / gammaOfDelta * (1 - EXP(-gammaOfDelta * (sprayDuration * 60))) *
                    EXP(-gammaOfDelta * (x * 7.8 - (sprayDuration * 60)))
            )
        )
    )

As an aside, MAKEARRAY is a slow function. It's better to use SEQEUNCE if you can, so in my version I've used SEQUENCE for the row and column headers:

=SEQUENCE(11,,7.8,7.8)
=SEQUENCE(,11,0.19,0.19)

P.b · Accepted Answer · 2024-05-09 17:39:30Z

1

This has to do with floating point calculation: https://spreadsheetweb.com/floating-point-calculation-issues-excel/

A workaround could be using index referring to your cells rather than a calculation of these values:

=MAKEARRAY(10,11,LAMBDA(x,y,IF(x*7.8<=Table2[Spray Duration (minutes)] * 60,XLOOKUP(INDEX(J4:T4,,y), Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[Rairborne(δ) (kg/s)]:[Rairborne(δ) (kg/s)]]) * (1 - EXP(-XLOOKUP(INDEX(J4:T4,,y), Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) * x*7.8)) / XLOOKUP(INDEX(J4:T4,,y), Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]),XLOOKUP(INDEX(J4:T4,,y), Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[Rairborne(δ) (kg/s)]:[Rairborne(δ) (kg/s)]]) / XLOOKUP(INDEX(J4:T4,,y), Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) * (1 - EXP(-XLOOKUP(INDEX(J4:T4,,y), Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) * (Table2[Spray Duration (minutes)]* 60))) * EXP(-XLOOKUP(INDEX(J4:T4,,y), Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) * (x*7.8 - (Table2[Spray Duration (minutes)] * 60))))))

(I used Table2, since in the app version I'm unable to change the table's name).

answered May 9, 2024 at 17:39

P.b

12k2 gold badges15 silver badges34 bronze badges

2 Comments

Nick Over a year ago

This is largely the best solution. I have approached a different way by just adding an additional index column to then perform the lookup on. Not a fan of these sort of work around but seems the floating point problem is not straightforward to resolve

P.b Over a year ago

Remember that this can be optimised using LET, as @FlexYourData demonstrated.

Collectives™ on Stack Overflow

Converting complex Formula into Dynamic Array

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related