07-14-2020 04:56 PM
07-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.
07-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.
07-14-2020 07:41 PM - edited 07-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.