SOLVED

Wrong cell reference

%3CLINGO-SUB%20id%3D%22lingo-sub-1522314%22%20slang%3D%22en-US%22%3EWrong%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522314%22%20slang%3D%22en-US%22%3E%3CP%3Ewhenever%20I%20drag%20the%20cells%2C%20the%20cell%20reference%20change%2C%20and%20it%20gives%20wrong%20values.%20It%20happens%20with%20all%20the%20formula%20I%20use.%20I%20am%20now%20doing%20a%20course%2C%20and%20I%20am%20calculating%20the%20trend%20value.%20The%20attached%20will%20show%20you%20the%20correct%20table%20that%20is%20given%20to%20me.%20I%20am%20using%20the%20same%20formula%20to%20replicate%20on%20the%20same%20sheet%2C%20and%20it%20gave%20me%20an%20error.%20The%20error%20is%20shown%20on%20red%20highlighted%20column.%20I%20tried%20to%20freeze%20the%20cells%20by%20using%20F4%2C%20but%20it%20gave%20different%20trend%20values%20when%20I%20compared%20it%20with%20the%20original%20column%20highlighted%20in%20green.%20Please%20help.%20Attached%20is%20the%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1522314%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1522340%22%20slang%3D%22en-US%22%3ERe%3A%20Wrong%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522340%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F728699%22%20target%3D%22_blank%22%3E%40suchetalahiri%3C%2FA%3E%26nbsp%3B%2C%20Looks%20like%20Column%20B%20is%20entered%20in%20as%20an%20Array%20Formula.%20To%20repllicate%20it%2C%20select%20U2%3AU31%2C%20type%20in%20the%20TREND%20function%20and%20hit%20CONTROL%2BSHIFT%2BENTER.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1522433%22%20slang%3D%22en-US%22%3ERe%3A%20Wrong%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522433%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3EHi%20Anthony%2C%20I%20did%20control%2Bshift%2Benter%2C%20but%20it%20didn't%20take%20the%20same%20cell%20reference.%20I%20am%20attaching%20the%20file%20for%20your%20perusal%2C%20thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1522441%22%20slang%3D%22en-US%22%3ERe%3A%20Wrong%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522441%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F728699%22%20target%3D%22_blank%22%3E%40suchetalahiri%3C%2FA%3E%26nbsp%3BTry%20locking%20the%20first%20cell%20that%20references%20the%20source%20cell%20by%20using%20the%20%22%24%22%20sign%20while%20referencing%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1522454%22%20slang%3D%22en-US%22%3ERe%3A%20Wrong%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522454%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F674460%22%20target%3D%22_blank%22%3E%40Ardamilola%3C%2FA%3EHello%2C%20thank%20you%20for%20your%20reply.%20I%20would%20request%20you%20to%20take%20a%20look%20at%20column%20T%20where%20I%20have%20locked%20the%20cells.%20However%2C%20the%20trend%20values%20are%20not%20matching%20with%20the%20exercise%20file%20that%20I%20have.%20The%20trend%20values%20given%20to%20me%20are%20visible%20on%20column%20D%20in%20green.%20If%20you%20would%20carefully%20look%2C%20both%20the%20column%20values%20are%20not%20matching.%20For%20example%2C%20the%20last%20cell%20values%20have%20a%20huge%20difference%20T31%20and%20D31.%20Please%20find%20the%20file%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1522484%22%20slang%3D%22en-US%22%3ERe%3A%20Wrong%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F728699%22%20target%3D%22_blank%22%3E%40suchetalahiri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Column%20D%2C%20the%20Array%20Formula%20%3DTREND(B2%3AB31%2CC2%3AC31)%20is%20not%20entered%20in%20cell%20D2%20and%20then%20copied%20down.%3C%2FP%3E%3CP%3EInstead%2C%20the%20range%20D2%3AD31%20is%20selected%20first%20and%20then%20placed%20this%20formula%20in%20D2%20and%20then%20confirm%20it%20with%20Ctrl%2BShift%2BEnter.%20That%20way%20the%20whole%20range%20D2%3AD31%20contains%20the%20same%20formula%20and%20D2%3AD31%20is%20considered%20as%20an%20Array%20Range%20and%20as%20a%20result%20you%20cannot%20delete%20an%20individual%20cell%20formula%20from%20the%20range%20D2%3AD31%2C%20try%20that%20yourself%20deleting%20any%20cell%20in%20the%20range%20D2%3AD31%20and%20you%20will%20get%20an%20error%20message%20which%20says%20%22You%20can't%20change%20part%20of%20an%20Array%20i.e.%20it%20is%20not%20a%20single%20cell%20Array%20formula%20but%20a%20multicells%20Array%20formula%20which%20means%20the%20same%20formula%20is%20placed%20in%20multiple%20cells%20at%20once.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20you%20have%20to%20do%20the%20same%20to%20replicate%20the%20formula%20of%20column%20D%20in%20column%20T%20i.e.%20select%20the%20range%20T2%3AT31%2C%20press%20F2%20key%20so%20that%20activecell%20T2%20will%20go%20into%20edit%20mode%2C%20paste%20the%20formula%20%3DTREND(B2%3AB31%2CC2%3AC31)%20in%20T2%20and%20then%20confirm%20it%20with%20the%20help%20of%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%3CP%3ETo%20know%20more%20about%20the%20Array%20Formula%2C%20you%20may%20go%20through%20this%20%3CA%20href%3D%22https%3A%2F%2Fkb.nmsu.edu%2Fpage.php%3Fid%3D75305%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Elink%3C%2FA%3E.%3C%2FP%3E%3CP%3EView%20the%20short%20video%20demo%20to%20know%20how%20to%20place%20this%20formula%20in%20column%20T.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1522511%22%20slang%3D%22en-US%22%3ERe%3A%20Wrong%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522511%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%20thank%20you%20sir%2C%20it%20worked%20for%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1522518%22%20slang%3D%22en-US%22%3ERe%3A%20Wrong%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F728699%22%20target%3D%22_blank%22%3E%40suchetalahiri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20take%20a%20minute%20to%20accept%20the%20post%20with%20the%20proposed%20solution%20as%20a%20Best%20Response%20to%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

7 Replies

@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.

@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.

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

@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.

@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.

 

@Subodh_Tiwari_sktneer  thank you sir, it worked for me.

Best Response confirmed by suchetalahiri (Occasional Contributor)
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.