Wrong cell reference

[suchetalahiri]

whenever I drag the cells, the cell reference change, and it gives wrong values. It happens with all the formula I use. I am now doing a course, and I am calculating the trend value. The attached will show you the correct table that is given to me. I am using the same formula to replicate on the same sheet, and it gave me an error. The error is shown on red highlighted column. I tried to freeze the cells by using F4, but it gave different trend values when I compared it with the original column highlighted in green. Please help. Attached is the file.

[#2]

@suchetalahiri , Looks like Column B is entered in as an Array Formula. To repllicate it, select U2:U31, type in the TREND function and hit CONTROL+SHIFT+ENTER.

[#3]

@TheAntonyHi Anthony, I did control+shift+enter, but it didn't take the same cell reference. I am attaching the file for your perusal, thank you.

[#4]

@suchetalahiri Try locking the first cell that references the source cell by using the "$" sign while referencing

[#5]

@ArdamilolaHello, thank you for your reply. I would request you to take a look at column T where I have locked the cells. However, the trend values are not matching with the exercise file that I have. The trend values given to me are visible on column D in green. If you would carefully look, both the column values are not matching. For example, the last cell values have a huge difference T31 and D31. Please find the file attached.

[#6]

@suchetalahiri 

In Column D, the Array Formula =TREND(B2:B31,C2:C31) is not entered in cell D2 and then copied down.

Instead, the range D2:D31 is selected first and then placed this formula in D2 and then confirm it with Ctrl+Shift+Enter. That way the whole range D2:D31 contains the same formula and D2:D31 is considered as an Array Range and as a result you cannot delete an individual cell formula from the range D2:D31, try that yourself deleting any cell in the range D2:D31 and you will get an error message which says "You can't change part of an Array i.e. it is not a single cell Array formula but a multicells Array formula which means the same formula is placed in multiple cells at once."

And you have to do the same to replicate the formula of column D in column T i.e. select the range T2:T31, press F2 key so that activecell T2 will go into edit mode, paste the formula =TREND(B2:B31,C2:C31) in T2 and then confirm it with the help of Ctrl+Shift+Enter.

To know more about the Array Formula, you may go through this link.

View the short video demo to know how to place this formula in column T.

[#7]

@Subodh_Tiwari_sktneer  thank you sir, it worked for me.

[#8 · Accepted solution]

@suchetalahiri 

You're welcome! Glad it worked as desired.

Please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.