My OFFSET function is not working in excel2021

Question

My OFFSET function works correctly on Computer A, retrieving a 3x1 range of cells.
However, when I switch to Computer B, no matter what I do, it only retrieves the value of the reference cell, as shown in the attached image.
I have confirmed that both the version and settings are consistent.

oliverscheurich · Answer

ali88saluja&nbsp;Do you have Office 365 or Excel for the web on computer A? In older versions OFFSET (or BEREICH.VERSCHIEBEN in german) only shows the value from the first cell.Which result do you get for=SUM(OFFSET(B2,0,0,3,1))&nbsp; &nbsp;on computer B?

ali88saluja · Answer

OliverScheurich&nbsp;Hi, thank you for your reply.I don't have office 365 and excel for the web on computer A.&nbsp;Both computers have LTSC 2021 version downloaded from the school.&nbsp;I get the result of 6 for=SUM(OFFSET(B2,0,0,3,1))&nbsp; &nbsp;on computer B.I think the function might be working correctly but just not displaying in an array format?&nbsp;

ali88saluja · Answer

oliverscheurich · Answer

ali88saluja&nbsp;In Excel for the web the formula spills the range. The screenshot in my earlier reply is from Excel 2013 where the range isn't spilled.In my understanding the formula works correctly on both of your computers. But since you work with Excel 2021 on both computers i wonder why computer A spills the range.

ali88saluja · Answer

OliverScheurich&nbsp;Thank you, I try&nbsp;running excel for the web version, and it worked successfully.&nbsp;However, the drawback of the web version is that it cannot run macros.&nbsp;I found that excel has the ability to spill the range since version 2019.It works on my other computers, but computer B is the only one with this issue, which is why I'm so confused.

Forum Discussion

My OFFSET function is not working in excel2021

Share

Resources