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
Bookmarks