+ Reply to Thread
Results 1 to 6 of 6

LINEST function only returns M and sometimes C values, all other values returned as #VALUE

  1. #1
    Registered User
    Join Date
    01-03-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    3

    LINEST function only returns M and sometimes C values, all other values returned as #VALUE

    Hi, I am using Excel 2016 on a 2018 MacBook Pro and I have been trying to use the LINEST function to get information from a dataset. I have used LINEST before on other machines without issue. Whenever I use the function now however, it always returns a value for M and the other values contain #VALUE!

    I found this error while doing data processing for a lab report so I tested it on a blank, new and simple file to see if the error repeats itself. On the lab report a value for M and C is returned and on the test file only a value for M is returned, with the others being errors.

    I use command + enter to fill the cells, and the cells do fill, only with this error. I have tried command + option + enter and various other combinations and command + enter seems to be the only one which at least fills the cells, be it not with the correct outputs.

    I have noticed that when navigating the output from the function, the data being read from set of cells box is not the same, the top left box for instance will read cells A1 to A10, the top right will read B1 to B10, the bottom left will read A2 to A11 and bottom right reads B2 to B11. This would explain why my lab report has a C value returned (as the cells next to the cells used to calculate the information are also filled with data). Basically I think the problem is that Excel Mac 2016 has an issue with reading the correct cells with the LINEST function, as everything works normally with Excel 2011 on an older Mac of mine.

    Any help with something I am doing wrong would be much appreciated. Thank you.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,071

    Re: LINEST function only returns M and sometimes C values, all other values returned as #V

    Any chance you can upload a sample file? I cannot recall any scenario where LINEST() did not return both M and C (it may have chosen to return 0 for either value, but it always gave me at least those two values without error) or all error values. I think that we may need to see this error in a spreadsheet to debug it.

    (To upload a spreadsheet, click on "reply to thread" or "go advanced" (or other way of getting to the main post editing page), then navigate down beneath the editing window to find the "manage attachments" link, which will bring up the site's file uploader.)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    01-03-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    3

    Re: LINEST function only returns M and sometimes C values, all other values returned as #V

    Hi, sorry I did try to upload the spreadsheet, clearly it didn't work. I'll try again now. I have also attached a screenshot of what I get when testing the function.
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,071

    Re: LINEST function only returns M and sometimes C values, all other values returned as #V

    It appears to me that you are misunderstanding the array nature of the LINEST() function. When I open your file, I see six copies of a LINEST() function, where only the first copy (E3) has a complete x,y data set as input. This copy is correctly outputting the slope, but, because the formula is not correctly array entered, the other values from the regression are lost/discarded. The other copies of the LINEST() function are the result of errors due to empty cells in the input ranges for those copies. My correction:

    1) Select range E3:F5
    2) Enter LINEST() function =LINEST(C3:C12,B3:B12,TRUE,TRUE). Like all array functions, confirm with ctrl-shift-enter (or the MAC equivalent, I've never used MAC so I do not know what the key sequence is on MAC).
    3) Review results to see if they are reasonable (I get an array of 2,0;0,0;1,0).

    If you dislike array entering LINEST() into a complete block of cells, you can nest LINEST() inside of INDEX() functions to return individual elements (intercept can be returned as =INDEX(LINEST($C$3:$C$12,$B$3:$B$12,TRUE,TRUE),1,2) for example -- note the absolute references to make copying easier), but I find this more tedious than entering LINEST() as a single block.

    Edit to add: According to https://support.office.com/en-us/art...bkmk_workcells control-shift-return is an equivalent key stroke for array entering formulas.
    Last edited by MrShorty; 01-03-2019 at 04:21 PM.

  5. #5
    Registered User
    Join Date
    01-03-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    3

    Re: LINEST function only returns M and sometimes C values, all other values returned as #V

    I've recorded a video showing my steps, they seem to be as you explained. The array actually changes the cells I select in the other cells (other than the one I initially select) the video should explain what I mean. I tried to use the $ sign to keep some values constant but it just gave me the gradient in all selected cells.

    The video however is too large to post and I cannot post links so I am unsure how to share it.

    https: //drive.google.com/open? id=1n6n5esPcgR68Cd351hNsclKNEc-uJbJH

    I have added the link but in three segments so it can post, it's just a Google Drive file.
    Last edited by Eroded_Plasma; 01-03-2019 at 05:17 PM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,071

    Re: LINEST function only returns M and sometimes C values, all other values returned as #V

    I cannot see your hands on the keyboard at the 18th second, but it appears that you are missing something in the array confirmation step when entering the LINEST() function. It looks like you are using ctrl-enter (or the MAC equivalent) which fills the selected range with the formula (equivalent to confirm entry in selected cell, copy cell, paste/fill into the range). This is not the same as array entry ctrl-shift-enter. Make sure that you are array entering the formula (if you missed the edits to my previous post, I found this https://support.office.com/en-us/art...mk_usefunction ) that says control-shift-return should be the way to array enter a formula).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1