+ Reply to Thread
Results 1 to 3 of 3

Change an Array formula to a normal formula

  1. #1
    Registered User
    Join Date
    03-12-2012
    Location
    liverpool
    MS-Off Ver
    Excel 2010
    Posts
    1

    Exclamation Change an Array formula to a normal formula

    Hi All,

    I need to change the formula below from an array to a normal formula. The formula basically looking through a list and finding the next larger match.the formula i need to change is;

    Please Login or Register  to view this content.
    Thanks for any help.

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Change an Array formula to a normal formula

    Hi jolpool!

    Welcome tot he forum..

    Did you tried DMAX formula.. its best in this scenario..

    =DMAX(Data!$A$1:$E$9999,"HeaderInE1",Sheet1!T1:U2)

    Where T1:U2 is helper column.. and like below..

    Capture.JPG

    If you are facing any challenges to adapt.. please upload a sample file.. for proper Fromula..
    Attached Images Attached Images
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,373

    Re: Change an Array formula to a normal formula

    use helper column. for instance column AA. In AA2 write:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    copy down to row 36351 and your main formula can be now:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    which is exactly the same as
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note that a benefit of array formula was, that you could easily copy it down. with non-array equivalent - you cannot do so.
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Code to Change Portion of Array Formula
    By Gos-C in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-28-2009, 12:57 PM
  2. Array Formula Vs Normal Formula
    By all4excel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-20-2007, 02:58 AM
  3. Variable Formula - both normal and array
    By willwonka in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2006, 02:40 PM
  4. How can I change a # in an array formula for a whole column?
    By gswegan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2006, 05:30 PM
  5. [SOLVED] Convert Normal formula to array formula
    By Pradip Jain in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-23-2005, 12:06 PM

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