Forum Discussion
Sum error Excel 2016 (Insider) Windows 10 Pro x64 (1809)
Sergei,
You didn't miss a thing which is what is frustrating me so much. The formula works on one sheet and not another.
I do think that I know why it's not functioning properly but have yet to come up with another way to express what I want to do.
That function is designed to spill if it's the last thing in a formula. Watching the execution of the formula you can trace it through the elimination of duplicates, and sorting, of all uniques in the array. When it hits the null (blank) cells it produces a 0 (zero) entry and the count is equal, BUT the next thing that happens is the final spill which produces a null entry which wipes out whatever is in row 22.
I THINK that's the cause of the apparent non-working formula. I have yet to come up with another way to do it but will look at it again tonight.
I also think that the UNIQUE function was tweaked since it was first released because it always put out one extra entry for null values before. In fact, that was one of my complaints about it. lol
John
Sergei,
I'm not sure what, if anything, is broken but I've found that I make the formulas work properly if I enter them as if they were the old Array Formulas by Entering the formulas into the top row, where I want it, and then using CTRL-SHIFT-ENTER. The formulas work and spill perfectly.
If I simply enter the formula in a blank cell, with sufficient rows for the spill, it spills correctly but the last entry is a 0 (Zero) which overwrites the "TOTALS" constant.
As I understand it you should NOT have to do that with the new Array Functions but that's the only way I can make them work.
Thanks,
Hear from you soon with any ideas I hope.
TheOldPuterMan
John
Hi John,
I guess an issue is not with UNIQUE but with using ROW() within your formula. Let consider this simple sample where we try to replace the value in row 3 on another value.
With
=IF(ROW()=3,-1,SEQUENCE(3,1))
my understanding is what the formula evaluates ROW() for the first cell in which you type it and after that keeps it static. Thus we always have condition 2=3 and formula returns the spill without any changes.
If the same formula convert to CSE one, ROW() will be evaluating for each element of the array and we receive desired result.
Same as if we generates an array not by CSE formula, but using an array in first parameter to compare with 3 - with DA experience each element will be compared and we have correct result.
Another story is what Formula Evaluate shows above not in very correct way.
Sample is attached.
Regards
.png){kind=link}