SOLVED

Highlighted
New Contributor

# Ignore blanks on a graph, when created by another formula: complicated

I have a table of student data over time for multiple students. I have hidden them and their performance, and allow the user to pick a student from a list to see just their performance over time in a single row.

As some students join after others, I needed a formula that did not see blank scores as 0, but as blank, as the student wasn't there when the data was collected, and a 0 score indicates they are on target. This then creates a graph for the user of performance against target over time.

My problem is that for those blank cells, for when the students had not joined at that time, the graph is showing their performance as zero, whilst I need it to not appear at all on the graph. My current formula is =IF((VLOOKUP(A2,A3:M79,COLUMN(),0))="","",VLOOKUP(A2,A3:M79,COLUMN(),0)).

Hopefully by looking at the file/screen shot, and the instructions provided it will make sense. In this case, the student's first data set never took place, as they wern't enrolled, but it is shown as a 0 on the graph, suggesting that they were performing at their target grade at the time.

2 Replies
Highlighted
Best Response confirmed by thortonisb (New Contributor)
Solution

# Re: Ignore blanks on a graph, when created by another formula: complicated

you could try replacing the "" with NA()

So

is =IF( VLOOKUP(A2,A3:M79,COLUMN(),0)="" ,NA() ,VLOOKUP(A2,A3:M79,COLUMN(),0) )
Highlighted

# Re: Ignore blanks on a graph, when created by another formula: complicated

@Wyn Hopkins That is fantastic, thank you. And yet, for some reason I think I tried that, but "NA()" so the ""made all the difference. Either way, you seem to have given me the solution I wanted so thanks!