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 Y1: =MAX(Y3:Y1002)This finds the current day in the day count. Put this in cell Z3 (or in any column O to Z…BR): =IF(Y3=$Y$1,FRACTIME(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:00This is the start time of your summing window. Put this in cell H4 (or in any static cell): 09:14:00This 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). |