SOLVED

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

Copper Contributor

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
best response confirmed by thortonisb (Copper Contributor)
Solution
you could try replacing the "" with NA()

So

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

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

1 best response

Accepted Solutions
best response confirmed by thortonisb (Copper Contributor)
Solution
you could try replacing the "" with NA()

So

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

View solution in original post