trishcollins
Board Regular
- Joined
- Jan 7, 2006
- Messages
- 71
I built a VERY long formula (25+ if statements), which if typed out completely, is too long and I cannot save the file. This portion of the formula, which will be repeated several times, which changes only to Priority, and Type, does work. I tested this one portion knowing that the row met all the conditions and I got the correct answer. At that point, I had already substituted the named range Condition_2 for the text, and that worked fine.
=IF(AND([@Priority]=2,Condition_2,([@[Current Balance]]-XLOOKUP([@[Extra Payments]],$C$3:$C$84,D$3:D$84))<=(D$110-(SUMIFS(D$114:D$119,[Priority],"<"&[@Priority])))),
[@[Current Balance]]-XLOOKUP([@[Extra Payments]],$C$3:$C$84,D$3:D$84),(D$110-(SUMIFS(D$114:D$119,[Priority],"<"&[@Priority]))))
The formula correctly returns $21,000
Because the entire formula is SO long , I have taken all the repeatable parts and put each into a named range.
Condition_1: '[@Type]="Line of Credit"
Condition_2: 'AND([@Type]="Mortgage",[@[Cumulative this year]]<=[@Maximum]) 'Ensures that the maximum allowed to contribute over regular payments has not been exceeded
Condition_3: 'D$110-(SUMIFS(D$114:D$119,[Priority],"<"&[@Priority])) 'Sum all the amounts already applied to higher priority items, and then deduct that total from any extra funds to see if there is any leftover to apply
Condition_5: '[@[Current Balance]]-XLOOKUP([@[Extra Payments]],$C$3:$C$84,D$3:D$84) 'Current balance less normal monthly payments
This formula, which replaces ALL the long formula parts with a named ranges for the above, evaluates correctly as "True", but then returns the "text" from Condition_5, rather than the calculated result of Condition 5.
=IF(AND([@Priority]=2,Condition_2,Condition_5<=Condition_3),
Condition_5, Condition_3)
I know this is just syntax, or possible the Indirect function around the xlookup, although I have tried that and it doesn't work, but I cannot seem to figure it out.
Any suggestions?
Trish![Wink ;) ;)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
=IF(AND([@Priority]=2,Condition_2,([@[Current Balance]]-XLOOKUP([@[Extra Payments]],$C$3:$C$84,D$3:D$84))<=(D$110-(SUMIFS(D$114:D$119,[Priority],"<"&[@Priority])))),
[@[Current Balance]]-XLOOKUP([@[Extra Payments]],$C$3:$C$84,D$3:D$84),(D$110-(SUMIFS(D$114:D$119,[Priority],"<"&[@Priority]))))
The formula correctly returns $21,000
Because the entire formula is SO long , I have taken all the repeatable parts and put each into a named range.
Condition_1: '[@Type]="Line of Credit"
Condition_2: 'AND([@Type]="Mortgage",[@[Cumulative this year]]<=[@Maximum]) 'Ensures that the maximum allowed to contribute over regular payments has not been exceeded
Condition_3: 'D$110-(SUMIFS(D$114:D$119,[Priority],"<"&[@Priority])) 'Sum all the amounts already applied to higher priority items, and then deduct that total from any extra funds to see if there is any leftover to apply
Condition_5: '[@[Current Balance]]-XLOOKUP([@[Extra Payments]],$C$3:$C$84,D$3:D$84) 'Current balance less normal monthly payments
This formula, which replaces ALL the long formula parts with a named ranges for the above, evaluates correctly as "True", but then returns the "text" from Condition_5, rather than the calculated result of Condition 5.
=IF(AND([@Priority]=2,Condition_2,Condition_5<=Condition_3),
Condition_5, Condition_3)
I know this is just syntax, or possible the Indirect function around the xlookup, although I have tried that and it doesn't work, but I cannot seem to figure it out.
Any suggestions?
Trish