Results 1 to 5 of 5

OFFSET vs MATCH in array formulas

Threaded View

  1. #1
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454

    OFFSET vs MATCH in array formulas

    Hi All,

    I'm trying to streamline a worksheet by exchanging OFFSET for MATCH.

    The sheet calculates a best fit curve by comparing various data after passing through three different models.

    Two of the models work fine, but the third is failing.

    I currently have this formula

    {=MMULT(MINVERSE($J$15:$K$16),MMULT(TRANSPOSE(OFFSET($B$2,$K$1,0):OFFSET($B$2,$L$1,1)),OFFSET($E$2,$K$1,0):OFFSET($E$2,$L$1,0)))}
    which I have replaced with this:

    {=MMULT(MINVERSE($J$15:$K$16),MMULT(TRANSPOSE(INDEX(ln_Time,$K$1+1):INDEX(Unity,$L$1+1)),INDEX(Buoyancy_loss,$K$1+1):INDEX(Buoyancy_loss,$L$1+1)))}
    where the named ranges are

    Buoyancy_loss=Regression!$E$2:$E$1000
    ln_Time=Regression!$B$2:$B$1000
    sqrt_time=Regression!$D$2:$D$1000
    unity=Regression!$C$2:$C$1000
    However, I'm generating a #NUM error. Can anyone see where I've screwed it up?

    Thanks
    Last edited by sweep; 10-01-2010 at 08:00 AM. Reason: Solved
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

Thread Information

Users Browsing this Thread

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

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