How to highlight specific data in dynamic charts.

[Liphor]

How do I highlight specific data in a various dynamic charts. I am trying to highlight in this case the maximum and minimum data in my dynamic charts. Attached is a copy of my excel.

[#2]

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

[#3]

@Riny_van_Eekelen 

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.

[#4]

@Liphor You can do that by declaring dynamic chart ranges. I've done that in the attached workbook. More information in the attached link.

https://trumpexcel.com/dynamic-chart-range/ 

[#5]

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?

[#6]

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

[#7]

@Riny_van_EekelenThank you and I understand your solution now. As for my error ,I guess it is because my formula for the named range involving maximum, counts cells "na". Attached here is my file that show my error for your perusal.

[#8]

@Liphor See attached. Now, the chart looks at three dynamic ranges RF, rfMax and rfMin (in that order) for the data and a dynamic range "Year for the X-axis labels.

When you create new dynamic ranges, like "namax" in your last file, you need to follow the instructions precisely. I.e. include the sheet name and use absolute cell references (i.e. with the $ signs). When I looked at the reference for "namax" it point to cells in column AD and the range was 50 rows long, because you inserted the COUNTIF based on column O rather than just picking up the number in H2. 

[#9]

Noted.Thank you.