fomc

FOMC Dot Plot Chart Using REPT Function

On Linkedin, a user commented on how he has been struggling to recreate 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

FOMC

Step 1:

Retype the percentage rates from top to bottom and apply necessary borders as shown below

FOMC

On the cell next to zero, type below formula

REPT

=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.

Fed dot plot chart

DownLoad Worksheet

RELATED ARTICLE

7 WAYS TO USE REPT FUNCTION

RECOMMENDED BOOK

Outside250

CHECK IT OUT!

4 thoughts on “FOMC Dot Plot Chart Using REPT Function”

  1. Rajan says:

    nice and cool stuff.

  2. 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 “*”

    1. Thanks Bill for the addition.

Leave a Comment:

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.