Hi Glenn, Trendie
Sorry for not replying but have been away.Glenn you are a genius. Your second formulae came up trumps. The problem I was having was transposing earlier cells which contained their individual formulae into cell 6. I found the solution by copying the actual numbers without the formulae for the calculations. I will then use excels graphing facilities to plot the actual close with the return of cell 6. Trendi thanks for your clever input. Even though you have put a cat amongst the pigeons. Ive been searching the web for the rules of Renko charts but as yet to no avail.
Thanks again for your generous assistance and education.
Kind regards ZigZag
Ps. Glenn do you have time to give me a text version of the formulae.
Hi Zigzag
I don't get called a genius every day - once in a lifetime will have to do -
I assume that cells A1and A2 speak for themselves.
The reason for having Cell D2 is so that you can change the number of reversal boxes by using a constant in that cell, which you can change without affecting any code. (Being an old-fashioned programmer from the 1960's I don't believe in putting data into programs !)
Cell A3 calculates the Difference between the two closes, which can be a negative or positive number.
The question now is whether the difference is big enough to generate a new point (or more than one) on your chart
Cell A4 divides the Difference in A3 by the Reversal constant to give you the number of Reversal boxes. The answer in Cell A4 could be a decimal or an integer (e.g. 1 or 1.25, or 3 or 2.1 etc etc) so you need to get rid of any decimal part of A4.
Cell A5 gets rid of the decimal part by truncating A4 to an integer only. This gives you an integer for the number of reversal boxes.
So now in A5 you have the number of reversal boxes which can be 0,1,2,3,4, ... or -1, -2, -3 etc etc.
Cell 6 contains a Nested IF statement broken down below. The idea is to do 3 tests -
If there is no new reversal, then do nothing.
If there is a positive reversal then calculate the new larger number.
If there is a negative reversal then calculate a new snaller number.
Breaking the whole nested IF down into its various parts:-
1.
IF((ABS(A5)<1),A1
If the number of reversal boxes in A5 is less than 1, then there is no new Close to plot, so put the previous close (A1) in A6.
2.
IF(A5>=0,(A5*$D$2+A1),
If the number of reversal boxes in A5 is greater than or equal to zero then calculate a new Close.
A5*$D$2 will be something like 0 * 20 or +1 * 20 or +2 *20 etc etc which is either zero or a positive integer.
A5*$D$2+A1 - by adding zero or a positive integer to A1 you will get a larger number or no change.
3. Now do the same for when A5 is a negative integer:-
IF(A5<0,( A1+A5*$D$2),
If A5 is Less than 0, then Cell A6 will contain whatever is in A1 less "A5 times the Reversal box size".
So if A1 is 100 and A5*$D$2 is 20 you will get 80 in A6.
4. And finally
IF(A5<0,( A1+A5*$D$2),
A1)))
i..e. If the number of reversal boxes is not less than zero - which has already been dealt with by the previous IF statement in 2. above, and so the logical sequence of processing will never get to this question - then put a default value of A1 in Cell A6.
Hope this explains well enough ?
Glenn
