Today line on a graph!

So I'm trying to design a line that will show a black line during what month it is. Just for visual ease.

I'm currently using....

=IF(ROW()-1=MONTH(NOW()),MAX($B$2:$B$17),NA())

I used this exact same equation before and it worked flawlessly. But for some reason, it is stuck only returning July as the month.

Any ideas? Could it be a formatting issue?

Originally Posted by SeanKosmann

So I'm trying to design a line that will show a black line during what month it is. Just for visual ease.

I'm currently using....

=IF(ROW()-1=MONTH(NOW()),MAX($B$2:$B$17),NA())

I used this exact same equation before and it worked flawlessly. But for some reason, it is stuck only returning July as the month.

Any ideas? Could it be a formatting issue?

I highly recommend attaching your workbook. No way to tell if it's a formatting issue without seeing your fomatting.

I don't think you have enough info here for us to solve. For example, you are comparing a row number to the current month--does each row contain data for one month, with January in row 2? If so, why are you looking down to row 17? What column does this formula appear in? What's in column B? Are months on the x-axis of your chart, and your black line is a vertical line?

All these questions are best answered with a quick look at your workbook.

Originally Posted by SeanKosmann

I used this exact same equation before and it worked flawlessly.

You must have changed something else about this file. The formula assumes that January is in row 2, and would work perfectly as long as that's true. But in this set of data, it is not true. The formula is doing exactly as told, which is to think that if we're on line #12, we must be in November.

I have a suggestion for you. Your dates in column A are simply typed in as text. I have changed this column so it they are dates, formatted to give you Month YYYY. Then I modified your formula to compare month to month, instead of row number to month.

Also, your data spans more than a year, so you have to compare month and year.

The max for the data series you selected to act as the flag is not the highest data point on the chart. You might want to switch it to the max for column E.

Originally Posted by SeanKosmann

...what I'm missing is, how did my previous formula show that I was starting with January?

The formula you used was

=IF(ROW()-1=MONTH(NOW()),MAX($B$2:$B$17),NA())

Let's deconstruct it.

ROW()-1

This expression means "take the row number of the cell where this formula appears, and subtract 1"

MONTH(NOW())

This returns a number giving the current month.

The formula compares the row number minus 1 to the month number to determine if the current row represents the current month. Therefore, for the formula to match January, it must be in row 2. Suppose today is 1/1/2010, and the formula is in row 2:

ROW()-1=MONTH(NOW()) =>
2-1 = 1 =>
1 = 1 =>
TRUE

Similarly, November will be on row 12 and for today's day you get

12-1=11 => 11=11 => TRUE

Your data did not start with January on row 2 (I am guessing that your original data did, and you changed it). Your data put July on row 12. Therefore your formula said:

12-1 = 11 => 11=11 => TRUE

So it lit up row 12 as the match, but the data in that row wasn't for November, like the formula assumed; it was for July.