Forum Discussion
How to highlight specific data in dynamic charts.
Liphor Just add the Minimum and Maximum as separate series. Make sure that the empty data points become #N/A by using NA() it the IF formula, rather than " ". Set the series overlap to 100% and reduce the gap width for the extra series slightly and colour each series to your liking.
See attached.
Thank you for your solution. However I realized that there is blank space in the charts that has fewer datas. I tried using countif and named range but not successful. How do I overcome this? Attached is the spreadsheet of what I have done.
Liphor You can do that by declaring dynamic chart ranges. I've done that in the attached workbook. More information in the attached link.
- I have look at your file and read the article recommended by you. However I did not understand why you use for eg ChartRF=OFFSET(Rainfall!$L$12,,,Rainfall!$K$7). Why is the $K$7. I create the following ranges; RF=OFFSET(Rainfall!$L$12,0,0,COUNTIF(Rainfall!$L$12:$L$61,">0")), Year=OFFSET(Rainfall!$K$12,0,0,COUNTIF(Rainfall!$K$12:$K$61,">0")), Max=OFFSET(Rainfall!$N$12,0,0,COUNTIF(Rainfall!$N$12:$N$61,"<>")). However I am not able to produce chart without blank space when the Max named range is added in the chart. What did I do wrong?
Liphor I merely used K7 to perform a count of the number of rows to be included in the chart.
The dynamic range starts on row 12 and a number of rows down, based on the value in K7.
Can't tell what you did wrong if you don't share your entire workbook, as long as it doesn't contain any confidential information.