Yesterday, I posted the a demonstration on how to make a Step Chart in Excel.
You can check out that post here:
How-to Easily Create a Step Chart in Excel
However, the process of setting up the data was a manual process.
So I thought that we should post a Friday Excel Challenge to see how we can automate creating the chart data range.
For this challenge, we will be using the history of the United States Federal Funds Rate (which is used in calculating the US Prime Rate). This data set has 341 data points, which would be very arduous to try and create the Step Chart data range manually.
The Challenge:
1) Download the US Prime Rate (Based on the Federal Funds Rate) data file.
2) Write a formula for cell C3 that can be copied down to make the Step Chart date range that will be used for the horizontal axis.
3) Write a formula for cell D3 that can be copied down to create the Step Chart rate series.
4) Copy the formulas down columns C and D to make your chart data range.
5) Create the Excel Step Chart
Here is a sample of what the original data series looks like:
Here is what your Excel chart data range will look like:
Here is a what your final Step Chart in Excel will look like:
Here is the Excel Spreadsheet with the Rate Data:
Step-Chart-Friday-Challenge-Data.xlsx
Submit your formulas in the comments section below (note that there is a delay of when you submit the comment and when you will see it on the website because I approve the comments to keep out spam bots).
It took me a few tries to make a really efficient formula. Good luck and come back next week to see the entries and also my demonstration on my technique. You can also download the final Excel file next week.
Thanks for your contributions in advance.
Steve=True
Steve, Here is my solution formulas:
C3 {=INDEX(Table1[Date],MATCH(INT(ROW(1:1)/2)+1,ROW(Table1[Date])-ROW($A$3)+1,0))}
D3 =IF(INT(ROW(1:1)/2)+1INT(ROW(2:2)/2)+1,INDEX(Table1[Rate],MATCH(C3,Table1[Date],0)),D2)
Just enter and drag the formulas down until they return N/A#, that is where the data set ends. Just delete the N/A# errors at the end, and then chart the data.
-Pete
Spoiler Alert, Pete rocked it. Great Job Pete. Works great in the file you sent 🙂 Not the same as mine, but similar. – Steve
I just sent my workbook.
C3 =IF(A4=””,INDIRECT(“A”&(ROW()+1-COUNT(A:A))),A4)
D3 =IF(A4=””,VLOOKUP(C3,Table1,2,FALSE),B3)
Thanks Don. Rock and Roll.
C3 INDEX(Table1[Date],ROUNDUP((ROW(A1)-ROW($A$1))/2,0)+1,1)
D3 INDEX(Table1[Rate],ROUNDUP((ROW(A2)-ROW($A$2))/2,0),1)
Thanks Leonid. I will have to check it out. Really appreciate your submissions to the Friday Challenge. Keep them coming. Steve=True
Thanks Leonid. Your C3 formula works perfectly for me. But the D3 (Rate) didn’t adjust for me correctly.
It was a typo. Lets try instead in D3:
=INDEX(Table1[Rate],ROUNDUP((ROW(A2)-ROW($A$1))/2,0),1)
Thanks, works great now! Steve=True