Add a Boolean "Flag" to Excel Line Charts

[YaoPau]

I'm trying to test a predictive model. The x-axis is the date, the outcome is the primary y-axis, and the indicator variable is the secondary y-axis.

I also have a 4th column on my excel sheet, which is a boolean flag for when the indicator variable "hits". So, for example, when the indicator variable rises 30 degrees in a 90 day span, the column equals "HIT", otherwise it's blank.

I'd like those "HIT" days to show up on my line chart automatically, ideally in the form of a vertical line, but anything that's clearly readable would do.

 

[YaoPau]

Any ideas? If it requires VBA I guess that's fine, though I'd rather not use it.

 

[VoG]

This might help you http://www.appspro.com/Utilities/ChartLabeler.htm

 

[Steve=True]

I think you can set up your data with dummy series that utilize NA() function in an if statement.

However, i don't understand what you extacly want it to look like.

Only plot the dummy series
dummy series =if(Value>=Target,Value,NA())

NA() is not plotted in most chart types.

Steve=True

 

[YaoPau]

I'm not sure I follow. Here's what I'm looking for...

I'm specifically looking to predict when the dependent variable will begin rising, and on my spreadsheet I've noticed that when the indicator variable rose quickly (>30 degrees Fahrenheit rise over the minimum temperature in the past 90 days), that usually triggers a rise in the dependent variable soon after. As a result, I'm charting both variables (currentTemp and dependentTemp) together on primary and secondary y-axes, with the Date on the x-axis.

I have a "HIT?" column that simply indicates the days when the indicator variable rose quickly. So basically it'd look like this, with HIT indicating that currentIncreasePrev90days >= +30.

date      currentTemp     dependentTemp      currentIncreasePrev90days            HIT?
3/13         90                  95                         +25             
3/12         89                  93                         +27 
3/11         92                  93                         +31                   HIT
3/10         93                  96                         +33                   HIT
3/09         91                  94                         +29

(Scroll right to see the HIT column!!) This is important to me because sometimes the indicator variable only rose, say, 26 degrees in the past 3 months, and it's hard for me to tell from just looking at the graph.

So what I'd like is for vertical lines to appear whenever the "HIT?" column has the value "HIT". So there'd be vertical lines on 3/10 and 3/11, but no lines for the other dates. That way I can easily see how effective the HIT indicator is, and also what percentage of all the dependent temperature rises I'm correctly predicting with the HIT indicator. Hope that's somewhat clear...

 

[shg]

You can plot an additional series. Instead of having the Hit column, use two columns containing the x and y values to plot where a hit occurs, and NA() where they don't. Then use a distinctive marker.

If you want a vertical line, you'll need an additional two-point series for each.

 

[YaoPau]

Ahh that makes sense. Is there a way to change the distinctive marker to a vertical line? I have green triangles at the bottom, but there's so much data and fluctuation in the charts that it's hard to see exactly where they line up.

Edit: Changing the HIT variable to a column graph does the trick Thanks everyone.

 

[Steve=True]

You can add an error bar to your points which can make them standout.