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
Step 1:
Retype the percentage rates from top to bottom and apply necessary borders as shown below
On the cell next to zero, type below formula
=REPT(CHAR(108),SUMPRODUCT((rates!$A$3:$A$18=chart!$B19)*(rates!B$3:B$18)))
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.
DOWNLOAD WORKSHEET Fed dot plot in Excel
RELATED ARTICLE
nice and cool stuff.
Thanks Rajan!
Great thoughts. I modified a bit. Put the Federal Reserve data in a spreadsheet. Sorted by rows, descending order. Immediately to the right, on the same sheet, I set up the display. the entry for my cell I3 is simply: =REPT(CHAR(108),B3). So B3 is the Fed data. This cell copies perfectly up and down the sheet.
thanks for turning my on to the REPT. And for those who don’t want to bother with the CHAR function, it appears you can replace it with something like “*”
Thanks Bill for the addition.
Such a great solution!!! Thank you for sharing.
Thanks, Maggie!
Glad you like it š