Two things I want to do with this tip.
I got the idea here a couple of months ago to use data labels (centered on the point) to "conditionally format" chart symbols. By linking the data label text to the spreadsheet cells (I use Rob Bovey's XY chart labeler add-in from appspro.com I understand that this functionality is native to Excel starting in 2013). Note that this will only allow you to format characters for the symbols based on your conditions. If you want to choose different colors or other formats, you will want to use a technique like this one: http://peltiertech.com/conditional-f...-excel-charts/
In this case, I am using a VLOOKUP() function to select the desired symbol from the lookup table. Note in particular how I am using TRUE as the 4th argument in the lookup function, so that Excel does not just look for the exact match, but will match anything within the ranges defined by the lookup values (left column) in the lookup table. It seems that almost all of the lookup examples on the internet are set up for exact match FALSE as the 4th argument -- which works fine for unsorted data or when you otherwise want to find exact matches. I see a lot of questions that can make use of this "approximate match" "binary search" option.
Bookmarks