One of the difficulties in calculating the CCI (stockcharts or Gstock) is you get can get a cell with a calculation like:

=(ABS(H3-G3)+ABS(H3-G4)+ABS(H3-G5)+ABS(H3-G6)+ABS(H3-G7)+ABS(H3-G8)+ABS(H3-G9)+ABS(H3-G10)+ABS(H3-G11)+ABS(H3-G12)+ABS(H3-G13)+ABS(H3-G14)+ABS(H3-G15)+ABS(H3-G16)+ABS(H3-G17)+ABS(H3-G18)+ABS(H3-G19)+ABS(H3-G20)+ABS(H3-G21)+ABS(H3-G22))/20

because the basic CCI calculation is the formula:

(typical price – n day simple moving average of typical price) / (.015 * mean deviation)

Where mean deviation is the average of the absolute values of the difference between the last n days typical price and the current n day simple moving average (hence the sum of abs(H3-Gx)).

If you want to change the number of periods from 20, to say, 99, then the formula becomes very tedious to enter. However, it seems there is an easier way to enter the formula in Excel (at least from a number of characters to type perspective) and that is to use array formulas (which I used in the medianifs calculation in a prior post). The first 11 columns are from the example in Gstock. Column G is simply the typical price (pivot value)

=(C3+D3+E3)/3

The first new column – which is column L – the first row is the number of periods, and the rest of the column is simply the formula

=(“G”&ROW()&”:G”&(ROW()+$L$1-1))

I did this primarily because I was having problems with getting the array function to work properly when this was part of the formula in column N, but it has the side effect of making the formula in m3 simple:

=AVERAGE(INDIRECT(L3))

The formula in column N is displayed in column O – remember this is an array function, so rather than pressing enter, you need to press ctrl shft enter.

The CCI calculation now becomes – for cell P3 :

=(G3-M3)/(0.015*N3)

Changing the value in L1 from 20 to 99 will recalculate the CCI in column P to be based on 99 periods.

The original Gstock spreadsheet example:

Data Table | Step-1 | Step-2 | Step-3 | CCI | |||||

Date | Open | High | Low | Close | Volume | ||||

09/19/1996 | 0.86 | 0.9 | 0.85 | 0.9 | ### | 0.883 | 0.823 | 0.033 | 121.425 |

09/18/1996 | 0.9 | 0.9 | 0.85 | 0.86 | ### | 0.870 | 0.818 | 0.032 | 107.904 |

09/17/1996 | 0.88 | 0.9 | 0.86 | 0.9 | ### | 0.887 | 0.815 | 0.030 | 160.638 |

09/16/1996 | 0.86 | 0.87 | 0.85 | 0.86 | ### | 0.860 | 0.811 | 0.026 | 125.160 |

09/13/1996 | 0.83 | 0.87 | 0.82 | 0.86 | ### | 0.850 | 0.809 | 0.024 | 117.092 |

9/12/1996 | 0.78 | 0.81 | 0.78 | 0.81 | ### | 0.800 | 0.808 | 0.023 | -23.090 |

9/11/1996 | 0.78 | 0.79 | 0.77 | 0.79 | ### | 0.783 | 0.810 | 0.024 | -74.023 |

9/10/1996 | 0.78 | 0.8 | 0.77 | 0.77 | ### | 0.780 | 0.811 | 0.024 | -87.470 |

9/9/1996 | 0.76 | 0.79 | 0.76 | 0.78 | ### | 0.777 | 0.808 | 0.027 | -78.407 |

9/6/1996 | 0.76 | 0.78 | 0.76 | 0.77 | ### | 0.770 | 0.805 | 0.029 | -79.563 |

9/5/1996 | 0.77 | 0.78 | 0.77 | 0.77 | ### | 0.773 | 0.805 | 0.030 | -69.475 |

9/4/1996 | 0.79 | 0.8 | 0.78 | 0.79 | ### | 0.790 | 0.803 | 0.032 | -28.070 |

And additional columns (L-P) showing the alternative calculation method:

Col L | Col M | Col N | Col O | Col P |

20 | Step 2A | Step-3A | CCI | |

G3:G22 | 0.823 | 0.033 | {=SUM(ABS(INDIRECT(L3)-M3))/$L$1} | 121.425 |

G4:G23 | 0.818 | 0.032 | {=SUM(ABS(INDIRECT(L4)-M4))/$L$1} | 107.9038 |

G5:G24 | 0.815 | 0.030 | {=SUM(ABS(INDIRECT(L5)-M5))/$L$1} | 160.6381 |

G6:G25 | 0.811 | 0.026 | {=SUM(ABS(INDIRECT(L6)-M6))/$L$1} | 125.1596 |

G7:G26 | 0.809 | 0.024 | {=SUM(ABS(INDIRECT(L7)-M7))/$L$1} | 117.0916 |

G8:G27 | 0.808 | 0.023 | {=SUM(ABS(INDIRECT(L8)-M8))/$L$1} | -23.0901 |

G9:G28 | 0.810 | 0.024 | {=SUM(ABS(INDIRECT(L9)-M9))/$L$1} | -74.023 |

G10:G29 | 0.811 | 0.024 | {=SUM(ABS(INDIRECT(L10)-M10))/$L$1} | -87.4704 |

G11:G30 | 0.808 | 0.027 | {=SUM(ABS(INDIRECT(L11)-M11))/$L$1} | -78.4067 |

G12:G31 | 0.805 | 0.029 | {=SUM(ABS(INDIRECT(L12)-M12))/$L$1} | -79.5626 |

G13:G32 | 0.805 | 0.030 | {=SUM(ABS(INDIRECT(L13)-M13))/$L$1} | -69.4752 |

G14:G33 | 0.803 | 0.032 | {=SUM(ABS(INDIRECT(L14)-M14))/$L$1} | -28.0702 |

Here is a spreadsheet with the calculations, including an alternative method using the offset() function.

gstock CCI with array function

August 29, 2012 at 12:38 pm |

thanks

February 20, 2013 at 11:16 pm |

={SUM(ABS(OFFSET(G3,0,0,$L$1,1)-M3))/$L$1}

February 24, 2013 at 4:39 pm |

Thank you for mentioning offset(). The offset function is a function which I expect to use a lot in the future.

August 16, 2015 at 6:38 pm |

Hello, I’m having problems with the formula described in the M column… appears #REF error, I would like you to provide the spreadsheet to download with the calculation of CCI, you can do it? would be easier to view the formulas, thank you.

August 30, 2015 at 6:11 am |

I have updated the post to include a link to a spreadsheet.it also includes the calculation using the offset() function.

October 2, 2016 at 2:27 am |

This formula looks forward. IE the result in row 3 is based on the values in rows 3 to 22… thats not how CCI works

Should be that the result in row 22 is based on values in rows 3 to 22

October 2, 2016 at 2:30 am |

Nevermind.. just noticed your dates are ordered most recent to oldest