• 468K Members
• 11.6K Online
• 566K Conversations
SOLVED

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
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) )

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies