
Originally Posted by
zanchin
. . . "a" is a very long formula and repeating it twice would make the overall formula very messy. Isn't there a leaner way? . . .
If you have a very long function which returns numeric values, and you want the positive values to be replaced by 0s, you have exactly 3 alternatives. I'll assume your long array formula is in cells C5:H5.
1. Since your Excel version has the FORMULATEXT function, it'd only take entering the following formula in a blank cell,
copying that cell, selecting C5:H5, pasting special as values, with C5:H5 still selected, pressing [F2] then [Ctrl]+[Alt]+[Enter] to enter the formula-generated formula as an array formula.
2. Use another range to come up with the final result. The final values would be INDEX((C5:H5<0),C5:H5,0).
3. Post your long formula because there may be something in it which could allow for a more elegant solution. Without details from you, expect only generalities from others.
Bookmarks