Example of a desired output:
=IFS(AN2<>"",AN2,AD2>0,AD2,Z2>0,Z2,Z2=0,0)
Alteryx rendering:
'=IFS(AN'+tostring([Count])+'<>"",AN'+tostring([Count])+',AD'+tostring([Count])+'>0,AD'+tostring([Count])+',Z'+tostring([Count])+'>0,Z'+tostring([Count])+',Z'+tostring([Count])+'=0,0)'
([Count] dynamically inserts the relevant row #.)
The desired outcome does get produced.
This would be found in the expected cell, if one were to look in it: =IFS(AN2<>"",AN2,AD2>0,AD2,Z2>0,Z2,Z2=0,0)
However a #NAME? error displays nonetheless, when one opens up the XLSX.
Until one selects the cell, simply clicks inside the formula bar, and simply hits enter.
Do that and the formula springs into dynamic life, w/o having to be altered in anyway (there not being a syntactical error).
But one doesn't want to have to futz w/ the output, if it can be helped.
So my workaround is to break the formula up into smaller chunks and insert them into a conditional if/then/elseif statement, outputting a different short and simple dynamic formula depending on this or that condition in other columns.
Like so:
if !isnull([future_retail]) then '=IF(AN'+tostring([Count])+'<>"",AN'+tostring([Count])+',AD'+tostring([Count])+')'
elseif !isnull([current_retail]) then '=IF(AN'+tostring([Count])+'<>"",AN'+tostring([Count])+',Z'+tostring([Count])+')'
else '=IF(AN'+tostring([Count])+'<>"",AN'+tostring([Count])+',0)'
endif
That works.
That averts having to manually activate the formula in Excel, after the fact.
It outputs something like one of three simpler formulas depending:
=IF(AN2<>"",AN2, AD2)
=IF(AN2<>"",AN2, Z2)
=IF(AN2<>"",AN2,0)
But is their a simpler way to output complex-ish dynamic Excel formulas, w/o changing the way one would input them when working directly in Excel? Something to do w/ formatting, or data type?
And is it the size/complexity of the formula that's generating the irksomeness? Or are there some Excel functions - IFS for example - for which the translation isn't going to be super smooth, for some reason?
Thanks!
-Trevor
