Trying to make a somewhat complex dyamic array.
I have the following set up in excel:
So I’m dynamically generating time and particle sizes (x an y values) to then calculate the array in the middle with the following formula:
Formula:
=
IF($B32 <= InputParameters[[Spray Duration (minutes)]:[Spray Duration (minutes)]] * 60,
XLOOKUP(K$31, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[Rairborne(δ) (kg/s)]:[Rairborne(δ) (kg/s)]]) *
(1 - EXP(-XLOOKUP(K$31, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) * $B32)) /
XLOOKUP(K$31, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]),
XLOOKUP(K$31, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[Rairborne(δ) (kg/s)]:[Rairborne(δ) (kg/s)]]) /
XLOOKUP(K$31, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) *
(1 - EXP(-XLOOKUP(K$31, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) * (InputParameters[[Spray Duration (minutes)]:[Spray Duration (minutes)]] * 60))) *
EXP(-XLOOKUP(K$31, Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[γ(δ)]:[γ(δ)]]) * ($B32 - (InputParameters[[Spray Duration (minutes)]:[Spray Duration (minutes)]] * 60)))
)
Lets just focus on the first 3/4 columns for now as the desired result:
I wish to convert all of this into a dynamic array. I am close but I am getting a N/A error for one of the y values and im unsure why:
The formula for dynamic array:
=MAKEARRAY(5,3,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)))
)))
In addition, I would also like to have included the 0 values where particle size and time are 0.
Here I am just specifying the exact timestep and particle step for ease.
So desired is outlined here:
I do not need to include particle size and time within this array.
Any advice is appreciated!