This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
The 2022.1.1.30569 Patch/Minor release has been removed from the Download Portal due to a missing signature in some of the included files. This causes the files to not be recognized as valid files provided by Alteryx and might trigger warning messages by some 3rd party programs.
If you installed the 2022.1.1.30569 release, we recommend that you uninstall it, and then install the latest 2022.1.1.30961 version.
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.
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 averts having to manually activate the formula in Excel, after the fact.
It outputs something like one of three simpler formulas depending:
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?