The INDEX(…MATCH(…)) function combination is very useful in SC spreadsheet calculations. It allows you to find values within defined ranges, and in SC’s descending-order spreadsheets. Here is an example:
Volume sum between a start time and an end time of the current day. Put this in cell Y3 (or in any column O to Z…BR): =IF(INT(A3)>INT(A4),Y4+1,Y4) This increments the day count. Put this in cell Z3 (or in any column O to Z…BR): =IF(Y3=MAX(Y$3:Y$1002),A3-INT(A3),0) This will extract the time of day of only the current day from the datetime of each bar in column A Put this in cell H3 (or in any static cell): 08:35:00 This is the start time of your summing window. Put this in cell H4 (or in any static cell): 09:14:00 This is the end time of your summing window. Note: The time in H3 must be earlier than the time in H4. Put this in cell H5 (or in any static cell): =SUM(INDEX(F$3:F$1002,MATCH($H$4,Z$3:Z$1002,-1),1):INDEX(F$3:F$1002,MATCH($H$3,Z$3:Z$1002,-1),1)) This is the sum of the volume between the start and end times of your summing window (assuming you have the default 1000 rows in your worksheet). The value should remain constant as bars/rows are added. |