Copying down Array formula gives wacky results

[SUPPO_USN]

I have the below array formula in cell AA6:

Please Login or Register  to view this content.

This provides the correct result, but when I copy and paste AA6 into AA7 - AA18 or grab the bottom right of AA6 and drag it to AA18, the cells below give wacky results and I can't figure out why. If I double click AA6, copy the formula, double click in AA7, paste the formula, then manually modify the needed cell references ("...- RDD'!$A6", "...- RDD'!$B6", "...- RDD'!$C6"), it works.

Data as it should be (with the manual update to each cell):

Please Login or Register  to view this content.

Data after dragging & dropping or copy/paste:

Please Login or Register  to view this content.

not being able to copy the formula with accurate results is frustrating and there are a number of cells that need to update. I have other array formulas in my spreadsheet and they copy/paste without any issues. Any help or suggestions are appreciated.

r/

SUPPO

[mikeTRON]

Can you post a sample file?

Also why bother with an array when the AVERAGEIF(s) formula exists?

[6StringJazzer]

To resolve this we need to see your data, not just the formula result. Can you attach your file?

[SUPPO_USN]

mikeTRON,

I was not aware of the AVERAGEIF(s) formula when tackling this problem. I managed to get a similar array formula working elsewhere in my sheet and modified it as needed in this instance.

As for a sample file, I'll see what I can do to get something posted. I will need to desensitize it prior to posting.

r/

SUPPO

[mikeTRON]

https://support.office.com/en-us/art...8-f7c5c3001690

[Tony Valko]

Also why bother with an array when the AVERAGEIF(s) formula exists?

AVERAGEIFS won't work in this case because there is array manipulation subtracting one range from another:

...IF('Data with Outliers'!$H$5:$H$1053>'Data with Outliers'!$G$5:$G$1053,'Data with Outliers'!$I$5:$I$1053-'Data with Outliers'!$H$5:$H$1053,'Data with Outliers'!$I$5:$I$1053-'Data with Outliers'!G4:G1052

[SUPPO_USN]

Sorry for the late response. It took a while to get the file sanitized. As a recap, I'm having issues with Column AA of the "Hold Time Analysis - RDD" worksheet. When I drag or copy the formula in Cell AA6 down to AA18, the results are NOT Correct. I manually calculated the answers in Column AD and those are the results that should be showing after dragging or copying. as I said above, if I double click AA6, copy the formula, double click AA7, paste the formula, and manually change the references, it works.

Any input on why this isn't working would be appreciated.

[MrShorty]

As near as I can tell, it is because the last reference in the formula ('Data with Outliers'!$I$5:$I$1053-'Data with Outliers'!G4:G1052) is a relative reference, instead of the absolute references used for all of the other references to "Data with outliers". I also note that the range in AA7 is different from AA6 (G5:G1053). It seems that it should work if you make that reference an absolute reference, and maybe check if AA6 should refer to G5:G1053 like the other cells in that column.

[SUPPO_USN]

It's always the little things that take the longest time to find. I'd looked at that code I don't know how many times and never noticed it wasn't an absolute reference like everything else and like it should have been. thanks for your extra set of eyes. 'Data with Outliers'!$I$5:$I$1053-'Data with Outliers'!$G$5:$G$1053 fixed my issue.

r/

SUPPO

[MrShorty]

If it helps in the future, I found the error by:
1) turning R1C1 notation on (temporarily). Unlike A1 notation, copied cells in R1C1 notation should have the exact same formula string (the text you see in the formula bar).
2) Scrolled through the column, looking for any changes or differences in the formula string. The only reference that I saw changing was the last one. In R1C1 notation, it can sometimes be easier to see the changes.
3) Then, studied the changing references to see why they are changing from copy to copy.
4) turn A1 notation back on.