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

@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
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies