SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-689867%22%20slang%3D%22en-US%22%3EIgnore%20blanks%20on%20a%20graph%2C%20when%20created%20by%20another%20formula%3A%20complicated%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-689867%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20of%20student%20data%20over%20time%20for%20multiple%20students.%20I%20have%20hidden%20them%20and%20their%20performance%2C%20and%20allow%20the%20user%20to%20pick%20a%20student%20from%20a%20list%20to%20see%20just%20their%20performance%20over%20time%20in%20a%20single%20row.%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20some%20students%20join%20after%20others%2C%20I%20needed%20a%20formula%20that%20did%20not%20see%20blank%20scores%20as%200%2C%20but%20as%20blank%2C%20as%20the%20student%20wasn't%20there%20when%20the%20data%20was%20collected%2C%20and%20a%200%20score%20indicates%20they%20are%20on%20target.%20This%20then%20creates%20a%20graph%20for%20the%20user%20of%20performance%20against%20target%20over%20time.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20for%20those%20blank%20cells%2C%20for%20when%20the%20students%20had%20not%20joined%20at%20that%20time%2C%20the%20graph%20is%20showing%20their%20performance%20as%20zero%2C%20whilst%20I%20need%20it%20to%20not%20appear%20at%20all%20on%20the%20graph.%20My%20current%20formula%20is%26nbsp%3B%3DIF((VLOOKUP(A2%2CA3%3AM79%2CCOLUMN()%2C0))%3D%22%22%2C%22%22%2CVLOOKUP(A2%2CA3%3AM79%2CCOLUMN()%2C0)).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20by%20looking%20at%20the%20file%2Fscreen%20shot%2C%20and%20the%20instructions%20provided%20it%20will%20make%20sense.%20In%20this%20case%2C%20the%20student's%20first%20data%20set%20never%20took%20place%2C%20as%20they%20wern't%20enrolled%2C%20but%20it%20is%20shown%20as%20a%200%20on%20the%20graph%2C%20suggesting%20that%20they%20were%20performing%20at%20their%20target%20grade%20at%20the%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-689867%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eblank%20cells%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-690039%22%20slang%3D%22en-US%22%3ERe%3A%20Ignore%20blanks%20on%20a%20graph%2C%20when%20created%20by%20another%20formula%3A%20complicated%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-690039%22%20slang%3D%22en-US%22%3Eyou%20could%20try%20replacing%20the%20%22%22%20with%20NA()%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20%3CBR%20%2F%3E%3CBR%20%2F%3Eis%20%3DIF(%20VLOOKUP(A2%2CA3%3AM79%2CCOLUMN()%2C0)%3D%22%22%20%2CNA()%20%2CVLOOKUP(A2%2CA3%3AM79%2CCOLUMN()%2C0)%20)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-691446%22%20slang%3D%22en-US%22%3ERe%3A%20Ignore%20blanks%20on%20a%20graph%2C%20when%20created%20by%20another%20formula%3A%20complicated%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691446%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3BThat%20is%20fantastic%2C%20thank%20you.%20And%20yet%2C%20for%20some%20reason%20I%20think%20I%20tried%20that%2C%20but%20%22NA()%22%20so%20the%20%22%22made%20all%20the%20difference.%20Either%20way%2C%20you%20seem%20to%20have%20given%20me%20the%20solution%20I%20wanted%20so%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
thortonisb
New 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
Solution
you could try replacing the "" with NA()

So

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

@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
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies