How to Spill a nested IF equation

[PrettyGood_Not Great]

Hi,

I have the following algorithm comprised of mostly nested IF. I am struggling to upgrade this to a 365 solution that will allow the equation to spill.

=IF($D11>=FcstMonth,IF(O$10>=$D11,IF(O$10<=$E11,$F11*O$9/SUM(XLOOKUP($D11,$O$10:$BF$10,$O$9#):XLOOKUP($E11,$O$10:$BF$10,$O$9#)),""),""),IF(O$10>=FcstMonth,IF(O$10<=$E11,$F11*O$9/SUM(XLOOKUP(FcstMonth,$O$10:$BF$10,$O$9#):XLOOKUP($E11,$O$10:$BF$10,$O$9#)))))

I don't expect anyone to come back with a working example to this puppy, just looking for any known approaches to handling the series of nested if. When I set all of the vertical references to spill (i.e. $D11# etc), I get a #VALUE.

 

[Cubist]

It would be helpful if you can post some sample data rather just a formula using XL2BB.

 

[Fluff]

Maybe

=BYROW(D11:F11#,LAMBDA(br,IF(INDEX(br,,1)>=FcstMonth,IF(O$10>=INDEX(br,,1),IF(O$10<=INDEX(br,,2),INDEX(br,,3)*O$9/SUM(XLOOKUP(INDEX(br,,1),$O$10:$BF$10,$O$9#):XLOOKUP(INDEX(br,,2),$O$10:$BF$10,$O$9#)),""),""),IF(O$10>=FcstMonth,IF(O$10<=INDEX(br,,2),INDEX(br,,3)*O$9/SUM(XLOOKUP(FcstMonth,$O$10:$BF$10,$O$9#):XLOOKUP(INDEX(br,,2),$O$10:$BF$10,$O$9#)))))))

 

[PrettyGood_Not Great]

It would be helpful if you can post some sample data rather just a formula using XL2BB.

Hi Cubist, here you go. Note I have removed some of the error handling so you can better see what it's doing.

Test.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O
8	Apr-24			161	15	15	19	5	37	42	35	5	7
9			ETC	188	143	143	180	150	143	180	150	120	173
10	TBA Start	Finish	TBA	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25
11	Jan-24	Jan-25	0.1	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
12	Jan-24	Jan-25	0.6	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.0	0.1
13	Jan-24	Jan-25	0.6	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.0	0.1
14	Jan-24	Jan-25	0.6	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.0	0.1
15	Jan-24	Jan-25
16	Jan-24	Apr-24			FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
17	Jan-24	Sep-24								FALSE	FALSE	FALSE	FALSE
18	Jan-24	Apr-24	39.4	39.4	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
19	Jan-24	Jan-25	7.4	0.9	0.7	0.7	0.8	0.7	0.7	0.8	0.7	0.6	0.8
20	Jan-24	Sep-24	18.9	3.8	2.9	2.9	3.6	3.0	2.9	FALSE	FALSE	FALSE	FALSE
21	May-24	Jul-24	0.3		0.1	0.1	0.1
22	Sep-24	Nov-24	91.1						27.5	34.7	28.9
23	Sep-24	Jan-25	7.6						1.4	1.8	1.5	1.2	1.7
24	Jan-24	Apr-24	15.0	15.0	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
SEM

Cell Formulas
Range	Formula
F8:O8	G8	=SUM(G11:G32)
F9:AW9	F9	=IF(F$10:AW$10="","",XLOOKUP(TEXT(F$10:AW$10,"mmm"),Monthlist,Hourlist))
F10:P10	F10	=TOROW(DATE(YEAR(FcstMonth),SEQUENCE(DATEDIF(FcstMonth,ProjFinsh,"m")+1,1,MONTH(FcstMonth),1),1))
F11:O24	F11	=IFERROR(IF($C11<>"",IF(G$10<>"",IF($C11>=$C$8,IF(F$10>=$C11,IF(F$10<=$D11,$E11*F$9/SUM(XLOOKUP($C11,$F$10:$AW$10,$F$9#,""):XLOOKUP($D11,$F$10:$AW$10,$F$9#,"")),""),""),IF(F$10>=$C$8,IF(F$10<=$D11,$E11*F$9/SUM(XLOOKUP($C$8,$F$10:$AW$10,$F$9#):XLOOKUP($D11,$F$10:$AW$10,$F$9#))))))),"")
Dynamic array formulas.

 

[PrettyGood_Not Great]

Maybe

=BYROW(D11:F11#,LAMBDA(br,IF(INDEX(br,,1)>=FcstMonth,IF(O$10>=INDEX(br,,1),IF(O$10<=INDEX(br,,2),INDEX(br,,3)*O$9/SUM(XLOOKUP(INDEX(br,,1),$O$10:$BF$10,$O$9#):XLOOKUP(INDEX(br,,2),$O$10:$BF$10,$O$9#)),""),""),IF(O$10>=FcstMonth,IF(O$10<=INDEX(br,,2),INDEX(br,,3)*O$9/SUM(XLOOKUP(FcstMonth,$O$10:$BF$10,$O$9#):XLOOKUP(INDEX(br,,2),$O$10:$BF$10,$O$9#)))))))

Hi Fluff, I'll give it a shot.

Here is some extra info

Test.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O
8	Apr-24			161	15	15	19	5	37	42	35	5	7
9			ETC	188	143	143	180	150	143	180	150	120	173
10	TBA Start	Finish	TBA	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25
11	Jan-24	Jan-25	0.1	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
12	Jan-24	Jan-25	0.6	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.0	0.1
13	Jan-24	Jan-25	0.6	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.0	0.1
14	Jan-24	Jan-25	0.6	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.1	0.0	0.1
15	Jan-24	Jan-25
16	Jan-24	Apr-24			FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
17	Jan-24	Sep-24								FALSE	FALSE	FALSE	FALSE
18	Jan-24	Apr-24	39.4	39.4	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
19	Jan-24	Jan-25	7.4	0.9	0.7	0.7	0.8	0.7	0.7	0.8	0.7	0.6	0.8
20	Jan-24	Sep-24	18.9	3.8	2.9	2.9	3.6	3.0	2.9	FALSE	FALSE	FALSE	FALSE
21	May-24	Jul-24	0.3		0.1	0.1	0.1
22	Sep-24	Nov-24	91.1						27.5	34.7	28.9
23	Sep-24	Jan-25	7.6						1.4	1.8	1.5	1.2	1.7
24	Jan-24	Apr-24	15.0	15.0	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
SEM

Cell Formulas
Range	Formula
F8:O8	G8	=SUM(G11:G32)
F9:AW9	F9	=IF(F$10:AW$10="","",XLOOKUP(TEXT(F$10:AW$10,"mmm"),Monthlist,Hourlist))
F10:P10	F10	=TOROW(DATE(YEAR(FcstMonth),SEQUENCE(DATEDIF(FcstMonth,ProjFinsh,"m")+1,1,MONTH(FcstMonth),1),1))
F11:O24	F11	=IFERROR(IF($C11<>"",IF(G$10<>"",IF($C11>=$C$8,IF(F$10>=$C11,IF(F$10<=$D11,$E11*F$9/SUM(XLOOKUP($C11,$F$10:$AW$10,$F$9#,""):XLOOKUP($D11,$F$10:$AW$10,$F$9#,"")),""),""),IF(F$10>=$C$8,IF(F$10<=$D11,$E11*F$9/SUM(XLOOKUP($C$8,$F$10:$AW$10,$F$9#):XLOOKUP($D11,$F$10:$AW$10,$F$9#))))))),"")
Dynamic array formulas.

 

[PrettyGood_Not Great]

Maybe

=BYROW(D11:F11#,LAMBDA(br,IF(INDEX(br,,1)>=FcstMonth,IF(O$10>=INDEX(br,,1),IF(O$10<=INDEX(br,,2),INDEX(br,,3)*O$9/SUM(XLOOKUP(INDEX(br,,1),$O$10:$BF$10,$O$9#):XLOOKUP(INDEX(br,,2),$O$10:$BF$10,$O$9#)),""),""),IF(O$10>=FcstMonth,IF(O$10<=INDEX(br,,2),INDEX(br,,3)*O$9/SUM(XLOOKUP(FcstMonth,$O$10:$BF$10,$O$9#):XLOOKUP(INDEX(br,,2),$O$10:$BF$10,$O$9#)))))))

Hi Fluff, this was the approach I was looking for (D11:F11# etc) so thanks for this. It is returning a #REF however. Calculation steps are not providing much insight. It shows a "BYROW(#REF,#VALUE)"

 

[Fluff]

Are D11, E11 & F11 all spill ranges that spill the same number of rows?

 

[PrettyGood_Not Great]

Are D11, E11 & F11 all spill ranges that spill the same number of rows?

D11 and E11 are named ranges of the same dimension. F11 is a calculation of it's own, but it uses named ranges that are the same dimension as D and E, so I think it's safe to say yes to all.

 

[PrettyGood_Not Great]

D11 and E11 are named ranges of the same dimension. F11 is a calculation of it's own, but it uses named ranges that are the same dimension as D and E, so I think it's safe to say yes to all.

On further inspection, F11 is calculating with a couple named ranges of small dimension that D and E. So this could be the problem array?

 

[Fluff]

If they are named ranges, then that is the reason. Try it like

=BYROW(D11:F24,LAMBDA(br,IF(INDEX(br,,1)>=FcstMonth,IF(O$10>=INDEX(br,,1),IF(O$10<=INDEX(br,,2),INDEX(br,,3)*O$9/SUM(XLOOKUP(INDEX(br,,1),$O$10:$BF$10,$O$9#):XLOOKUP(INDEX(br,,2),$O$10:$BF$10,$O$9#)),""),""),IF(O$10>=FcstMonth,IF(O$10<=INDEX(br,,2),INDEX(br,,3)*O$9/SUM(XLOOKUP(FcstMonth,$O$10:$BF$10,$O$9#):XLOOKUP(INDEX(br,,2),$O$10:$BF$10,$O$9#)))))))