baitmaster
Well-known Member
- Joined
- Mar 12, 2009
- Messages
- 2,042
I'm using the following array formula to create a text string that joins all numbers in a range, allowing me to compare two such ranges to see if all totals are the same:
=TEXTJOIN("|",FALSE,ROUND(M27:M38,0))
which by default I expect to return the values
0|0|0|0|0|0|0|0|0|0|0|0
I have two such formulas looking at two identical sets of results, so I want to test for equality. The strangest thing though, is that only one of them will ever return this result. The other returns
|||||||||||0
Even stranger, when I correct one and recalculate, the other changes. If I cut and paste either to another location it changes to align with the other, but the other then changes when I recalculate. Copy/paste from either to the other does not fix the problem, the formula is identical but gives different results.
In most cases the one that returns 0|0|0|0|0|0|0|0|0|0|0|0 actually also contains some missing values e.g. 0|0||0|0|0||0|0|0||0
The contents of M27:M37 are zero sums, M38 is empty. Formats in source cells are #,##0,;(#,##0,);"- "
There's no circular references, calculations are on, and I've cleared formatting in all areas.
Changing the sums to pasted 0s gives the right results even though the actual values are unchanged. Copy > Paste values confirms they are all zeroes in the sums. Copying and pasting one zero sum to another does not solve the inconsistent results 0||0||0
Can anyone explain this weirdness?!
=TEXTJOIN("|",FALSE,ROUND(M27:M38,0))
which by default I expect to return the values
0|0|0|0|0|0|0|0|0|0|0|0
I have two such formulas looking at two identical sets of results, so I want to test for equality. The strangest thing though, is that only one of them will ever return this result. The other returns
|||||||||||0
Even stranger, when I correct one and recalculate, the other changes. If I cut and paste either to another location it changes to align with the other, but the other then changes when I recalculate. Copy/paste from either to the other does not fix the problem, the formula is identical but gives different results.
In most cases the one that returns 0|0|0|0|0|0|0|0|0|0|0|0 actually also contains some missing values e.g. 0|0||0|0|0||0|0|0||0
The contents of M27:M37 are zero sums, M38 is empty. Formats in source cells are #,##0,;(#,##0,);"- "
There's no circular references, calculations are on, and I've cleared formatting in all areas.
Changing the sums to pasted 0s gives the right results even though the actual values are unchanged. Copy > Paste values confirms they are all zeroes in the sums. Copying and pasting one zero sum to another does not solve the inconsistent results 0||0||0
Can anyone explain this weirdness?!
