2

I have a problem with TEXTJOIN formula and couldn't understand why it behaves wierdly. Any help is much appreciated.

I am trying to join number and text based on condition whether the number is positive or negative, grouping them together.

This is how my data looks like and the texts are joined at the last row: enter image description here

My formula is as follow 

={TEXTJOIN(", ",TRUE,IF(SIGN(B2:B13)=SIGN(B14), TEXT(B2:B13,IF(B14>0,"+","")&"$0.0,,")&" "&C2:C13,"")) &" Offset by "&
TEXTJOIN(", ",TRUE,IF(SIGN(B2:B13)<>SIGN(B14), TEXT(B2:B13,IF(B14<0,"+","")&"$0.0,,")&" "&C2:C13,""))}

The problem is, as I hit Ctrl+Alt+F9, sometimes the output shows correctly: enter image description here

But sometimes the output is incorrect, with the last reference array missing (i.e. the "OTHERS" comment): enter image description here

I need to have a consistent and correct output as there are a few of this formulas in the workbook.

Does anyone has any workaround? Is this a Excel 2016 bug?

Thanks! Greatly appreciate your help!

Rosetta
  • 2,665
  • 1
  • 13
  • 29
  • Do you have anything else in your file? I can't reproduce the same error. (Works for me) – Pierre44 Apr 16 '18 at 15:55
  • @Pierre44, i create a new file for the example above, there is nothing else in the file... – Rosetta Apr 17 '18 at 02:31
  • I have updated it with the exact same file but I never get anything missing... – Pierre44 Apr 17 '18 at 14:13
  • 1
    @pierre44, thanks for the effort. I think it due to huge data. I use the function TextJoin in another working, again it's inconsistent (showing error when it should not). I'm using a workaround, but thanks! – Rosetta Apr 19 '18 at 03:24

1 Answers1

0

Yes excel formulae do get buggier as they compute with more rows. In my experience 10,000 rows is where things really start getting unreliable. You can usually get around this by splitting long formulae into different cells e.g. placing your two TEXTJOINs into two different cells, then using a third cell to concatenate.

Dharman
  • 30,962
  • 25
  • 85
  • 135
MTwem
  • 135
  • 1
  • 1
  • 9