Jul 14 2020 04:56 PM
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.
Jul 14 2020 05:11 PM
@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.
Jul 14 2020 06:36 PM
@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.
Jul 14 2020 06:43 PM
@suchetalahiri Try locking the first cell that references the source cell by using the "$" sign while referencing
Jul 14 2020 06:52 PM
@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.
Jul 14 2020 07:41 PM - edited Jul 14 2020 07:43 PM
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.
Jul 14 2020 08:09 PM
@Subodh_Tiwari_sktneer thank you sir, it worked for me.
Jul 14 2020 08:16 PM
SolutionYou'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.
Jul 14 2020 08:16 PM
SolutionYou'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.