+ Reply to Thread
Results 1 to 14 of 14

Summing the max value of each row in an array (with one formula).

  1. #1
    Registered User
    Join Date
    01-16-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Summing the max value of each row in an array (with one formula).

    Hopefully someone can help with this, I’ve tried the usual avenues such as Google to no avail:

    I have a number of control programs for a 24 hour period which can be combined to form an overall daily operating percentage. Where two or more programs contain levels for the same hour the maximum value is taken, this is done on an hour-by-hour comparison. These are then added up to form the equivalent daily operating percentage.

    The workbook is attached which should be more self-explanatory.

    The eventual formula would ideally not depend on any intermediary calculations or contain a repetition of the same formula over different ranges (e.g. concatenation of all the formulas above ref A in attached workbook).

    Unfortunately VBA cannot be relied upon... that would be too easy… it needs to work with no requirement of the user to do anything more than open the workbook.

    I have made some way into a solution (ref B in workbook). It uses a bit masking approach to achieve combinations by adding the program IDs together, then it uses OFFSET to iterate through the hours and find the max for each row. It works as an array over cells, but gets weird when summed, it seems to just take the first value in the array for all of the 24 hours (ref C in workbook). There seems to be some inherent problem with this method so I’ve come here in hope of a fresh approach.

    Many, many thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Summing the max value of each row in an array (with one formula).

    Hi Will,

    Welcome to the forum.
    You said "it iterate through the hours and find the max for each row"... so does that means it would take the highest value in the column against the ID entered in Z39. I am still not clear what you are looking for because Program Ids never repeated (i.e, 1,2,4, 8,16 etc) and then they have only relevant column above them to look for. Please explain using example and highlight those rows / columns / cells. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    01-16-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Summing the max value of each row in an array (with one formula).

    DILIPandey, thanks for the fast response;

    The program IDs are added up, such that Program Code 3 means programmes 1 and 2 are applied. Simlarly Program Code 5 means progams 1 and 4 are applied. Essentialy each program ID is the decimal value of a binary bit in each position (2^n).

    Here's an example for Program Code 5 being applied:
    Program ID 1 2 4 Result
    00:00 0 0 50 50
    01:00 0 25 50 50
    02:00 100 25 50 100
    03:00 0 25 20 20
    ... ... ... ... ...
    23:00 0 25 0 0

    I need the formula to the return sum of the result column in above example, which would be 220 (ignoring the hours past 03:00).

    In workbook attached to first post, the cell Z39 can be used to test a program code, the result showing in Z32 and above. These work fine, but, I need it to work as one formular so it be used as inteded in the table from D46. I can then expand it to cover 7 days. The formular in AC32 is my attempt but for some reason it doesn't work as expected.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Summing the max value of each row in an array (with one formula).

    Hi will,

    Basis your recent example, designed the solution you need. See the attachment and let me know if this helps you to some extent.


    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-16-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Summing the max value of each row in an array (with one formula).

    Thanks DILIPandey, but that's not quite what I'm after. My example in the second post was just to illustrate what needs to happen. The final solution will need to look at all 24 hours and with any combination of programs.

    This is my soultion from the example workbook I posted: =SUM(IF(C8:C31,MAX(IF(MOD(INT(Z39/E39:W39),2),OFFSET(E7:W7,C8:C31,0),0)),0),0)

    I might be over complicating things slightly, but the problem is considerably more complicated that you've given it credit.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Summing the max value of each row in an array (with one formula).

    Hi Will,

    I find your first post and the attachment, a little confusing / hard to understand. Would suggest you to include your question in my recent attachment and post back as this would be slight easier to understand. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  7. #7
    Registered User
    Join Date
    01-16-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Summing the max value of each row in an array (with one formula).

    I've amended your sheet accordingly.

    The cells highlighted in green are the inputs, which the user will be able to adjust as they want.

    The cells highlighted in yellow are what I need, however these depend on the cells below them which is not acceptable. The cells higlighted in red should, in theory, be the soultion, but for some reason it doesn't work.
    Attached Files Attached Files

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Summing the max value of each row in an array (with one formula).

    Quote Originally Posted by WillReynolds View Post
    This is my soultion from the example workbook I posted: =SUM(IF(C8:C31,MAX(IF(MOD(INT(Z39/E39:W39),2),OFFSET(E7:W7,C8:C31,0),0)),0),0)
    Hi WillReynolds,
    With MAX function, simple using:
    Assuming A2=7; B2=8; A3=5; B3=6
    MAX(A2:B2)=8
    MAX(A3:B3)=6
    Sum of MAX:
    MAX(A2:B2)+MAX(A3:B3) = 14
    Unfortunately, Bill Gates does not allowed Sum of MAX in array like this: SUM(MAX(OFFSET(A1:B1,{1,2},))) which equals SUM(MAX(OFFSET(A1:B1,{1},))) and equals SUM(MAX(A2:B2)) = 8
    So without VBA, we should use helper column to get MAX of each row and sum helper.

  9. #9
    Registered User
    Join Date
    01-16-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Summing the max value of each row in an array (with one formula).

    Thanks bebo021999, I thought it was coming up against the capabilities of Excel. Curiously though it works if entered as a multi-cell formula, but not if summed as single cell formula. VBA and helper cells are just not elegant enough, I’ll keep searching for an alternative way to accomplish the same thing.

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Summing the max value of each row in an array (with one formula).

    HI Will,

    I have gone through the revised attachment and would like to say that still I am not able to understand it.
    Not sure how you are getting Program Code and on what is the logic to obtain the daily hours. May be you are explaining too much or is it me who is missing some key things here. To keep it simple, you have the data in Range a1:u25 and now I just want to know what ouput you want and basis what logic... since you already have formulas which are picking up the number(s) but the logic is still missing in mind. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  11. #11
    Registered User
    Join Date
    01-16-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Summing the max value of each row in an array (with one formula).

    I guess the crux of the problem is captured by the title "summing the max value of each row in an array". It's quite easy to do this if helper cells are set up to sum each row, however in this case multiple permutations are possible and the array is generated on the fly.

    It's for calculating the energy usage of the lighting within a large building where each light fitting is controlled by a predetermined program of when it's on and how bright. A light fitting could be controlled by any combination of programs and takes the maximum brightness where programs conflict.

    For example, a light is set to be controlled by two programs; 100% brightness between 10:00 and 18:00 and 25% brightness constantly. The light would then be on 25% 00:00 until 10:00 then 100% until 18:00 and then 25% again until 00:00. A total of 12 hours at 100% brightness.

    So, as an input the formula takes the programs applied to a light fitting, and outputs the total equivalent number of hours a day it would be at maximum brightness.

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Summing the max value of each row in an array (with one formula).

    @WillReynold,
    I 'd like to discuss abt your formula:
    i.e Z31:
    =MAX(IF(MOD(INT(Z$39/$E$39:$W$39),2),$E31:$W31,0))

    1- in which: $E$39:$W$39 contents array of constant (1,2,4,8,...)
    Z$39/($E$39:$W$39) = {17,8.25,4.25,2.125,...)
    INT(Z$39/$E$39:$W$39) = {17,8,4,2,...}
    MOD(INT(Z$39/$E$39:$W$39),2) = {1,0,0,0,1,...}
    with number 1 at 1st and 5th position. This array is fixed at all.

    2- In comparation array with $E31:$W31, Z31 means:
    MAX({E31,0,0,0,I31,0,...} equals: IF(I31>E31,I31,E31)
    Therefore, with row 39 is fixed, we can use IF function instead of MAX

    3- With IF, we can sum max of each row as follow: in AC32
    IF(I8:I31>E8:E31,I8:I31,E8:E31) will returns array of MAX value of each row, equals {0.25,0.25,0.25,....}
    SUM(IF(I8:I31>E8:E31,I8:I31,E8:E31)) = 16.5
    Donot forget to confirm by Ctrl-shift-enter

    Hope this help.
    Last edited by bebo021999; 01-18-2012 at 10:01 AM.
    Quang PT

  13. #13
    Registered User
    Join Date
    01-16-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Summing the max value of each row in an array (with one formula).

    Thanks bebo021999. The value in Z39 is changable however, so it can not be assumed I8:I31 and E8:E31 are the columns in use.

  14. #14
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Summing the max value of each row in an array (with one formula).

    Hi Will

    I have developed a kind of interest in this.
    I hope you have read my comment "To keep it simple, you have the data in Range a1:u25 and now I just want to know what ouput you want and basis what logic..."

    I feel you are explaining quite good and nicely putting up things here, would like to know the practical aspect as well. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ 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