Sum error Excel 2016 (Insider) Windows 10 Pro x64 (1809)

Brass Contributor

Having a weird error trying to sum a column of values. The formula is written so that if a key cell is empty (""), the array that the formula (-1) will be summed and the total appears in that cell.

 

The results are always 0.00 in the cell until you look at the execution of the formula. When the condition to trigger the SUM occurs the correct total is produced (+$224.23) BUT is immediately overwritten by the results of the "FALSE" side of the IF statement which is $0.00. The FALSE branch would be taken if the trigger column was not = ""... but not when it is.

 

Normally, when evaluating a formula, if you hit a FALSE condition the next step replaces the TRUE code with an #N/A and the next step it to execute the first step in the FALSE branch. Not so here. Both sides of the TRUE/FALSE statement are intact and after executing the FALSE statements the TRUE statements are executed.

I know, confusing but I am stuck!!

I've attempted to attach 3 screenshots showing what is happening. (1) shows the formula as is, (3) is after evaluation to the correct total (+$224.23) and (4) shows the end result of $0.00.

 

I hope I'm just really tired lol (Recovering from 2 surgeries in 11 days, drugs DO NOT help the thought process).

 

Thanks to anyone than tosses a bone my way. If the screenshots didn't come through, give me a yell. They tell the tale.

 

Thanks,

 

TheOldPuterMan

John

 

 

8 Replies

John, I was not able to reproduce on a simple model. Are you on the latest Insiders build (1904.11601.20042)? Previous one was quite buggy.

 

@TheOldPuterMan 

@Sergei Baklan 

 

Sergei,

 

Yes, I am. In fact, the (one) reason I'm so slow in getting back is that I did a system reset and Microsoft left me in in a bad spot. The updated deleted ALL of my USB drivers and did not install any new ones.

 

I had to dig out my trusty old PS/2 keyboard and remember how to work my way through device manager with tab and cursor keys. I finally got a set of USB drivers installed for the mouse and then started rebuilding my system.

 

I didn't lose data but the past few days have been Interesting. On top of it my wife walked in with the chest/head cold from hades and had to share it with me. I've been down with that for the past 4 days and haven't done anything.

 

I appreciate your helpful suggestions and THINK I have an inkling to what's happening. 

 

I'll get back to you later, after more testing.

TheOldPuterMan

John

 

@Sergei Baklan 

 

I think what's happening here is that I'm trying to execute a new set of commands from within the Spill of the Array commands and it's getting confused.

 

Below are the two formulas, the first that works most of the time and the second that produces a blank cell ("") as its result.

 

Works: {=IF(COUNTA(UNIQUE(Transactions!Expense_Center))+2=ROW(),"TOTALS",SORT(UNIQUE(Transactions!Expense_Center)))}

Doesn’t Work: {=IF(COUNTA(UNIQUE(Transactions!Expense_Center))+2=ROW(),"TOTALS",SORT(UNIQUE(Transactions!Expense_Center)))}

 

Both are running against a 1 x 300 array on another (Transactions) sheet. 

 

What I get, from the second, non-working, formula is that in the final step, as it should fill the cell with "TOTALS" is a blank cell from the Spill of the UNIQUE function. Prior to this, there was always a single entry of 0 (zero) that represented the blank values in Transactions!Expense_Center. The above formula looked at that, compared the 0 (zero) to the null ("") and found a true condition which caused the cell to be loaded with "TOTALS".

 

Now though seems to no longer work.

 

As far as I know, I've had no Office updates and other than the messed up update to 1809 no O/S updates that should affect this.

 

ANY help here I'd love it. I have tried separating the formula into different steps but haven't had success there yet.

 

Thanks for looking,

 

TheOldPuterMan

 

John

Hi John,

 

Sorry to hear you had such challenges.

As for the formulas I see no difference between these two

{=IF(COUNTA(UNIQUE(Transactions!Expense_Center))+2=ROW(),"TOTALS",SORT(UNIQUE(Transactions!Expense_Center)))}

{=IF(COUNTA(UNIQUE(Transactions!Expense_Center))+2=ROW(),"TOTALS",SORT(UNIQUE(Transactions!Expense_Center)))} 

Did I miss something?

 

However, I'll try to play some later based on your description.

@TheOldPuterMan 

John, perhaps I misunderstood the issue, tried both with empty and null cell with your formula, and with one which returns the same (non-array)

=IF(LEN(SORT(UNIQUE(exp)))=0,"Total",SORT(UNIQUE(exp)))

- all work

image.png

simple model is attached

@Sergei Baklan 

 

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

@TheOldPuterMan 

 

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

@TheOldPuterMan 

 

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.

image.png

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