Data messed up with certain changes, and can't undo?!

[mchlbrmn]

Hello There.
With your valuable help, my Excel file makes an array of data, resorting it from the data arranged by the week collected from a group of mice, to a new array at the right with the data sorted by the age of the mouse in weeks. Here is the formula to get the new array sorted by age:

=IFERROR(_xlfn.SINGLE(INDEX(4:4,AGGREGATE(15,6,COLUMN($I$2:$AK$2)/(ROUND(($I$2:$AK$2-$G4)/7,0)=LOOKUP(2,1/(AM$2:$AN$2<>""),AM$2:$AN$2)),LEFT(AM$3)+0))),0)

It works well . However, if I make certain changes to the Excel sheet the array repopulates with all zeros. If I undo the change, it is still all zeros. The first change was to insert a new row under this array between before some other calculations below. The second time I added a column to the left that averaged the data from a row of the array. Same thing when computer is restarted.

Why does this happen?

Thanks in advance.

Microsoft Office Professional Plus 2019. Excel version 1808 (I think).

[bebo021999]

Normally, the AGGREGATE function does not required Ctrl-shift-enter.
But: ROUND(($I$2:$AK$2-$G4)) inside that an array formula, therefore see below instruction:

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[AliGW]

Microsoft Office Professional Plus 2019. Excel version 1808 (I think).

Please update your forum profile accordingly. Thanks.

[mchlbrmn]

Bebo, I already see brackets around the entire formula. I notice that when I select and copy a formula, like when I pasted it on the forum, the brackets disappear. That's why it didn't appear above, but I see it in my workbook.

It still destroys itself if I insert a line under the array.

I'm not sure what they mean to press "Ctrl, Shft, Enter".
If I put the =AVERAGE function after my array to average that row of the array, the result is zero, and that row of the array becomes zeroes. If I enter the function with CTRL+Shft, the result is the same, but the formula does have brackets around it.




AliGW: I hear and I obey.

[JeteMc]

It may help if we could see a sample file. It may also help to know what the _xlfn.SINGLE... portion of the formula is supposed to do as that was not part of the last formula that bebo provided in the "LOOKUP: Trying to deglitch repeats in data" thread.
Let us know if you have any questions.

[mchlbrmn]

I was wondering about that. I figured that if the Excel Gods must had inserted the _ilfn, it must be good.
I could be wrong.

[JeteMc]

The last formula bebo provided was: =IFERROR(INDEX(4:4,AGGREGATE(15,6,COLUMN($C$1:$F$1)/(ROUND(($C$1:$F$1-$B5)/7,0)=LOOKUP(2,1/($H$1:H$1<>""),$H$1:H$1)),LEFT(H$2)+0)),"")
As this article states an .xlfn prefix indicates a function that is not supported in the version of Excel that you are currently running.
https://support.microsoft.com/en-us/...4-9fbb77fd5025
I am not sure what .xlfn.SINGLE is supposed to be or what it is supposed to do.

[mchlbrmn]

Thank you JeteMc. I have trouble keeping track of all these Excel details that are new to me.

I've been working back and forth with a colleague, so maybe we have compatibility issues with our Excel versions. Other format stuff gets messed up a bit too. I'll try to get both of us to save the document in a common version. I hope that both our versions support the functions in the formula.