+ Reply to Thread
Results 1 to 4 of 4

Maximum Value of Alternate Columns/Rows

  1. #1
    Registered User
    Join Date
    10-30-2013
    Location
    Nepal
    MS-Off Ver
    Excel 2010
    Posts
    7

    Post Maximum Value of Alternate Columns/Rows

    I want to find out the Maximum value of Alternate Columns or Alternate Rows of a range, such as
    the maximum Value Column C10, E10,G10, I10 of range B10:I10 - in case of Columns, and
    the maximum Value RowC10, M10,M12,M14,M16 of range M9:M16 - in case of Rows.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    MS Office Excel 2007
    Posts
    5,025

    Re: Maximum Value of Alternate Columns/Rows

    Try
    =MAX(IF(MOD(COLUMN(C10:i10),2)=1,C10:I10)
    Array formula, use Ctrl-Shift-Enter
    =MAX(IF(MOD(ROW(M10:M16),2)=1,M10:M16)
    Array formula, use Ctrl-Shift-Enter
    Last edited by Special-K; 12-07-2017 at 10:03 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    1,775

    Re: Maximum Value of Alternate Columns/Rows

    For Alternate Column.
    =MAX(IF(MOD(COLUMN(B10:I10)-1,2)=0,B10:I10))

    For Alternate Row.
    =MAX(IF(MOD(ROW(M9:M16),2)=0,M9:M16))

    Both formula confirmed as array (CTRL + SHIFT + ENTER).

    Edit: Special-K beat me to it.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    MS Office Excel 2007
    Posts
    5,025

    Re: Maximum Value of Alternate Columns/Rows

    Quote Originally Posted by CK76 View Post
    For Alternate Column.
    =MAX(IF(MOD(COLUMN(B10:I10)-1,2)=0,B10:I10))

    For Alternate Row.
    =MAX(IF(MOD(ROW(M9:M16),2)=0,M9:M16))

    Both formula confirmed as array (CTRL + SHIFT + ENTER).

    Edit: Special-K beat me to it.
    Actually I didn't as I copied the first formula and forgot to change COLUMN() to ROW() :-(
    So technically you beat me to to the correct answer :-)

+ Reply to Thread

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