+ Reply to Thread
Results 1 to 13 of 13

Max value between every nth cell - blocks of data

  1. 11-19-2012, 03:23 PM #1
    sans
    sans is offline
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Max value between every nth cell - blocks of data

    Hello,

    I am trying to find a way to get the max value between every nth cell. In the "Data" sheet you can see the blocks of results (in reality they are more than a hundred) which are spaced out every 3 columns. You can see the max value in the sheet named "Max". I tried to adapt various formulas I found on the forum but didn't manage to get it right.

    Basically in the attachment, you can see a MAX(D7,A7,AAP7) formula pulled down, which will take a lot of time to complete. Can this be done faster with a formula or vba?

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-19-2012, 03:53 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Max value between every nth cell - blocks of data

    not sure if this will help or not, but it easier than finding the cell references...

    =MAX(OFFSET(Data1!A1,6,3),OFFSET(Data1!A1,6,15),OFFSET(Data1!A1,6,27))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 11-19-2012, 03:58 PM #3
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Max value between every nth cell - blocks of data

    Try this regular formula on your Max sheet
    B7: =MAX(INDEX((Data1!$5:$5=B$5)*Data1!7:7-((Data1!$7:$7="")+(Data1!$5:$5<>B$5))*(10^10),0))

    It will search Row_5 on the Data1 sheet for all columns that contain the specified letter and return the max value of the referenced row for them.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules
    Reply With QuoteRegister To Reply
  4. 11-19-2012, 04:00 PM #4
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Max value between every nth cell - blocks of data

    Try using an "array formula" like this in Max sheet cell B7

    =MAX(IF(Data1!$5:$5=B$5,Data1!7:7))

    confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar then copy across and down
    Audere est facere
    Reply With QuoteRegister To Reply
  5. 11-19-2012, 04:05 PM #5
    sans
    sans is offline
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Max value between every nth cell - blocks of data

    Thank you for your reply FDibbins!

    The formula results are correct with the 3 blocks of data. However if I have let's say 100 blocks of data spaced out, I will need a massive formula. Is it possible to somehow calculate every 13th cell up to let's say up to the 4000 nth cell, without having to repeat the offset part?

    Many thanks!
    Reply With QuoteRegister To Reply
  6. 11-19-2012, 04:42 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Max value between every nth cell - blocks of data

    did you try any of the other suggestions offered?
    Reply With QuoteRegister To Reply
  7. 11-19-2012, 05:04 PM #7
    sans
    sans is offline
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Max value between every nth cell - blocks of data

    Hi guys, thanks for your replies! Just went to grab something to eat

    I just had a go with the suggestions and they both work great. Thank you! Is there any essential difference between the solution in post #3 and post #4? Or is it just another way of achieving the same result?

    Many thanks!
    Reply With QuoteRegister To Reply
  8. 11-19-2012, 05:18 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Max value between every nth cell - blocks of data

    Rons formula is a standard formula, and uses very little resources. DDL offered an array formula which can get resource-heavy
    Reply With QuoteRegister To Reply
  9. 11-19-2012, 05:20 PM #9
    sans
    sans is offline
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Max value between every nth cell - blocks of data

    One last question, in case I would like to include 2 additional sheets named, Data2 and Data3, how would I include them in the calculation?

    Many thanks
    Reply With QuoteRegister To Reply
  10. 11-19-2012, 05:21 PM #10
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Max value between every nth cell - blocks of data

    I suspect that my formula will be more efficient as it has fewer function calls and calculations.......but Ron's is preferable if you want to avoid "array entry"
    Reply With QuoteRegister To Reply
  11. 11-19-2012, 06:03 PM #11
    sans
    sans is offline
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Max value between every nth cell - blocks of data

    Is it possible with the above solutions to add more Data sheets in the calculation? I have been trying to do it but I keep receiving errors for some reason.

    Many thanks!
    Reply With QuoteRegister To Reply
  12. 11-19-2012, 07:02 PM #12
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Max value between every nth cell - blocks of data

    You can separate an IF function for each sheet with commas within MAX function like this

    =MAX(IF(Data1!$5:$5=B$5,Data1!7:7),IF(Data2!$5:$5=B$5,Data2!7:7),IF(Data3!$5:$5=B$5,Data3!7:7))

    still needs CTRL+SHIFT+ENTER
    Reply With QuoteRegister To Reply
  13. 11-20-2012, 04:17 PM #13
    sans
    sans is offline
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Max value between every nth cell - blocks of data

    Thank you very much DDL, it works absolutely flawless! Thank you to everyone for all your help!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1