Spreadsheet: 488582d1478721527-code-problem-help-needed-gap-calculation-gap-1-
Sheet: flat

 |  |  |  |  |  | LOGIC | B | C | D | E
 |  |  |  | 4030.35 |  | manually feed data | open level(@5:30 | HIGHEST level in DAY | Lowest Level in DAY | Close level @23.00
 |  |  |  |  |  | data that is calculated |  | formula for C and D column are different
2015-07-02 00:00:00 | 4026.5 | 4049 | 3981.15 | 4026.9 |  | calculated field in B is % difference of Close  of Previous day & opening Level of corrent day
 | -0.1 |  |  | -0.09 |  | (done one row above other calculations as there formulas of Time comes)
 |  | 0.46#-1.22% | UP#0.56#-1.13% |  |  | here for example current day's data is 2 row above the calculated filed
2015-07-03 00:00:00 | 3993.4 | 4020 | 3977.1 | 3954 |  | Previous Day close is in columnE of 5 Rows above the calculated field and 3 Rows above the manual feed fields
 |  | not counted |  |  |  | calculated field in C is
2015-07-06 00:00:00 | 3978.7 | 3999.5 | 3940 | 3958 |  | (1)if % difference of Close  of Previous day & opening Level of corrent day(calculated field in B) is >0.6, will display "GU" means GapUP
 | 0.62 |  |  |  |  | ELSE "GD" (Gap Down)----- if % difference is > -0.6
 |  | GU#1.15#-0.35% | DW#0.52#-0.97% |  |  | (2)% difference high of current day VS close of previous day
2015-07-07 00:00:00 | 3982.2 | 3984 | 3940 | 3962 |  | (3)% difference LOW of current day VS close of previous day
 | 0.61
 |  | GU#0.66#-0.45% | DW#0.05#-1.06% |  |  | calculated field in D is
2015-07-08 00:00:00 | 3986.2 | 3989 | 3978 | 3981 |  | (1)if % difference of Close  of current day & opening Level of current day(calculated field in B) is >0.6, will display "UP"(UP)
 | 0.61 |  |  |  |  | ELSE "DW" (Down)----- if % difference is > -0.6 |  |  | Delimitor between results is "#"
 |  | GU#0.68#0.4% | [NAME] |  |  | (2)% difference high of current day VS open of current day |  |  | base in calculation is close of previous day
2015-07-09 00:00:00 | 3984.8 | 3988 | 3944.9 | 3999 |  | (3)% difference LOW of current day VS open of current day
 | 0.1
 |  | 0.18#-0.91% | UP#0.08#-1% |  |  | If I may have made mistakes in making formula , the logic is final NOT THE FORMULA
2015-07-10 00:00:00 | 3947.7 | 3962 | 3918.9 | 3999.35
 |  |  |  |  |  |  |  |  | Delimitor between results is "#"
 |  | not counted |  |  |  |  |  |  | base in calculation is OPEN of current day
2015-07-13 00:00:00 | 3974.7 | 3985 | 3949 | 3980.6
 | -0.62 |  |  |  |  | NOW there can be 6 combination of details I need that has to be mentioned before GU/DU in formulas in C-not any seperation/delimiter required
 |  | GD#-0.36#-1.26% | 0.26#-0.65% |  |  | and before UP/DW  formulas in D -not any seperation/delimiter required
2015-07-14 00:00:00 | 3956.5 | 3993.8 | 3964.05 | 3989.5 |  | in formulas in C | in formulas in D
 | -0.61 |  |  |  |  | GU for two continuous day | UP for two continuous days |  | +2
 |  | GD#0.33#-0.42% | UP#0.94#0.19% |  |  | GU for three continuous day | UP for three continuous days |  | +3
2015-07-15 00:00:00 | 3965.1 | 3990.8 | 3957.7 | 3985.1 |  | GD for two continuous day | DW for two continuous days |  | -2
 | -0.61 |  |  |  |  | GD for three continuous day | DW for three continuous days |  | -3
 |  | GD#0.03#-0.8% | UP#0.65#-0.19%
2015-07-16 00:00:00 | 3960.9 | 3989.2 | 4156 | 3988.4 |  | Please make it as separate formulas so I can reusethat in other formulas
 | -0.61
 |  | GD#0.1#4.29% | UP#0.71#4.93%
2015-07-17 00:00:00 | 4186 | 4196.95 | 4164.1 | 4185