Forum Discussion
Using an array formulas to return more than 255 characters of text based on a condition in excel
I use excel spreadsheets to monitor performance on a quarterly basis. In addition to numeric values, reports include a descriptions/narratives. However, sometimes the narrative information provided exceeds 255 characters. I use If and IFS array formulas to produce a status report, based on the most up to date information. i.e. at the end of the third quarter, if no report was provided for an indicator, data for q2 would be presented, if nothing is there for q2 then the q1 data is presented. However, where the more than 255 characters a #Value error is returned.
https://opmjm-my.sharepoint.com/:x:/g/personal/adrian_booth_opm_gov_jm/ETcYd8igPZJNhnccW7NkclIBYBYHX-pPy6OWl1a1Bj9fiQ?e=42bTsi
Note if the formula is NOT an array it works for each row. However, that is not a practical solution given the fact I am tracking over a thousand of rows and I have found changing all my formulas to array functions 'lightens' the workbook.
Thank you for any insight or workarounds anyone can provide.
2 Replies
- That is a limitation of Array Formulas.
My suggestion is to make your Table formatted as a Table (Home -> Format as a Table) and then when you enter a formula into a column it auto-populates the whole column so each formula is a 'single' value that looks at @[column] per se and appears (in my test) to have no problem returning x-long strings.- Thank you for your suggestion.
For the specific application I have found using format as tables limiting and have shied away from it. If there is no other way I may have to resort to it.
I have found using the LEFT formula as a surprising workaround to successfully return x-long strings in array formulas (e.g. LEFT(A1:A500,5000). However, when seeking to add conditions with IF or IFS, it doesn't work (#Value error).
