In the dashboards course you demonstrate how to make a chart with the min and max values highlighted. I would like to do something similar with year over year (circle the latest month and the same month a year previous). I'm playing around with a formula using ISBLANK and OFFSET to locate that previous month but I ended up with the previous month number (yeah!) right beside the latest month number instead of on the line of that previous month (oops). Any suggestions?
Hi Brenda,
Can you please share the file, or if it's confidential, please create a mock-up that illustrates the issue.
Mynda
Hi Mynda,
Attached is a mock-up of the table / chart I'm working on. In the original file the source table in A1:C18 is a pivot table so I'll have to work on that aspect too. Struggling to make the two concepts (Min/Max + pivot table) play together nicely. I'm thinking I can figure that out now that I've watched all of the Dashboard sessions.
I've never used Offset before, so I'm still trying to get my arms around it. You can see that I started with working through the Min/Max points, but that was just as a practice. Probably won't keep it in the finished chart.
In the end I'd like to be able to show more than 12 months, but highlight the final month + the same month a year previous, and add text stating the % change YOY.
Thanks for any help you can offer!
Brenda
Hi Brenda,
Nothing is attached.
After you select the file you need to click Start upload to load it to the site.
Phil
So sorry. Let me try again.
In the meantime I have worked out a way to accomplish the YOY comparison (included in the now attached file), but I'm still stumped on the best way to make this all work with a pivot chart.
Brenda
Hi Brenda,
Congratulations on figuring it out yourself!
Pivot Charts are too restrictive to create a chart with different points highlighted as you have in your file.
The best you could do is have a hybrid chart that references a PivotTable for some of the data. You can see how to create a regular chart from a PivotTable in lesson 5.02.
Mynda
