+ Reply to Thread
Results 1 to 7 of 7

Need MAX formula to include column references from changing ranges

  1. #1
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Need MAX formula to include column references from changing ranges

    We use a spreadsheet to assemble vendor quotes.

    Depending on the number of price breaks desired, we need to add a lead-time column for that quote, and illustrate the longest lead time for that part from a variety of non consecutive columns.

    The summary, would have a formula such as: =MAX(B3,E3,H3,K3) to provide the longest lead-time for the part. That vendors prices would be in columns C,D,G,H,I,J etc.

    Problem arises, if we add a vendor to the group, the MAX formula will not include any new fields without manually adding a cell reference from the new group to the MAX formula.

    The MAX cell references can be spaced x number of columns apart, and can be anywhere from 2-10, but always the same on any particular spreadsheet.

    So, how to have our MAX formula automatically include the new columns added or deleted from the spreadsheet?

    TIA for any ideas.

    Pete

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Need MAX formula to include column references from changing ranges

    I would pull the max data OUT of the report you are in and put them in a tidy table in a reference worksheet in the background, so your formula will expand with ease. You could then use the table to bring back the reporting data as well.
    Really, excel is usually about normalizing your data to make extremely simple formulas. The only time that doesnt work is when you are forced to use some format that is suboptimal.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Need MAX formula to include column references from changing ranges

    Thank you mikeTRON. Go Stars!

    Pete

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need MAX formula to include column references from changing ranges

    Quote Originally Posted by PeteABC123 View Post
    Go Stars!
    You misspelled Pens!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Need MAX formula to include column references from changing ranges

    TB shouldn't even have a team. Pennies in 4.

  6. #6
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Need MAX formula to include column references from changing ranges

    Surprisingly I DO like the Penguins.

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need MAX formula to include column references from changing ranges

    There are some very wise people participating in this thread.

+ 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. Replies: 4
    Last Post: 08-05-2014, 11:33 AM
  2. [SOLVED] Help with changing column references in sumproduct
    By Gdespont in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-27-2013, 01:48 PM
  3. Help with changing multiple =SUM column references
    By chrisober in forum Excel General
    Replies: 1
    Last Post: 06-17-2012, 03:25 AM
  4. Replies: 3
    Last Post: 10-03-2011, 03:19 PM
  5. Formula in cells with changing references
    By Idiotfool in forum Excel General
    Replies: 3
    Last Post: 03-16-2009, 09:41 AM
  6. Copy a formula while changing column references
    By sauron3000 in forum Excel General
    Replies: 2
    Last Post: 06-24-2008, 09:34 AM
  7. insert column without changing references
    By mrsmac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-10-2005, 02: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