my formula is for example: (JUST EXAMPLE) SUM(IF(C>H, H, 0),20)-C The formula will become error since C can not be greater than H in each period and the left ¡°c¡± in SUM() is not equal in period to the right ¡°c¡± My objective is that I want the left ¡°c¡± to be the same period of the right ¡°c¡± i.e. for one period of sum(¡­..), I should have only one and the same period of ¡°c¡±(but not as in the formula 20C in 20 periods ago) to compare with the 20 different H in all 20 periods ago. In brief, I just want the left ¡°c¡± to be the same as right ¡°c¡± in the same period. I have tried the following methods before but all fail, e.g. X:=VALUEWHEN(1, CUM(1), C) SUM(IF(X>H, H, 0),20)-C BUT IT FAILS AS X IS NOT EQUAL TO RIGHT "C" IN THE SAME PERIOD BUT EQUAL TO H i have also tried the alternative method, e.g. 0+IF(C>REF(H,-1), REF(H,-1), 0)+IF(C>REF(H,-2), REF(H,-2), 0)+...... BUT I WANT TO ADD UP TO 200 PERIODS BUT THE FORMULA BOX HAS CHARACTERS LIMIT SO IT FAILS TOO

Hey VBB- Welcome to the forum! When you get a sec, please have a look at the forum rules. We discourage against using vague subjects such as "Urgent help"; bumping 8 hours later; and, using all capital letters. Regarding your question, post the code that you need 'urgent' help with. In your example, you might be on the right track with something like you already posted: IF(C>ref(H,-1), C, ref(H,-1). If the current close is greater than the high one period ago, then use the current close, else use the high one period ago.

Check out the PREV function in the User's Manual or in the Formula Primer. Maybe something like this? x:=IF(C>REF(H,-1), REF(H,-1), 0) + PREV; x;

thank you for your help first, but if use PREV and CUM, the "C" itself becomes variable too which i don't want to see, your formula is sth like this x:=IF(C>REF(H,-1), REF(H,-1), 0) + IF(REF(C,-1)>REF(H,-2), REF(H,-2), 0)+ PREV; x; but my objective is like this: IF(C>REF(H,-1), REF(H,-1), 0) + IF( C >REF(H,-2), REF(H,-2), 0)+................. i really appreciate your help!

thank you everyone for your help, all of you are so kind and really helpful. Besides i want to say thousands of thanks to JOSE and your formula solves my obessive problem and wish you good luck in the future!