On Linkedin, a user commented on how he has been struggling to recreate the Federal Reserve dot Plot. After being puzzled for almost 2 years, he came to realize how REPT function in excel can be of great help.
After searching the web, I have found there is no comprehensive article on how to recreate this FOMC dot report.
So, I decided to replicate the dot plot on this site.
Below is a step by step procedure on how to create a dot plot chart using the percentage interest rates and the Federal Open Market Committee (FOMC) expectations shown below
Retype the percentage rates from top to bottom and apply necessary borders as shown below
On the cell next to zero, type below formula
How it Works
- REPT function will repeat a dot, CHAR(108), the number of times supplied by the SUMPRODUCT function.
- SUMPRODUCT((rates!$A$3:$A$18=chart!$B19)*(rates!B$3: B$18))–looks up the percentage rate and returns the number of FOMC participants in the above table.
NB: Apply the Wingdings font so that CHAR(108) can appear as a dot.
- Apply some background color, Different font colors to differentiate the months and some different borders.