Discussion Re: Ignore blanks on a graph, when created by another formula: complicated in Excel
https://techcommunity.microsoft.com/t5/excel/ignore-blanks-on-a-graph-when-created-by-another-formula/m-p/690039#M32764
you could try replacing the "" with NA()<BR /><BR />So <BR /><BR />is =IF( VLOOKUP(A2,A3:M79,COLUMN(),0)="" ,NA() ,VLOOKUP(A2,A3:M79,COLUMN(),0) )Thu, 13 Jun 2019 12:26:36 GMTWyn Hopkins2019-06-13T12:26:36ZIgnore blanks on a graph, when created by another formula: complicated
https://techcommunity.microsoft.com/t5/excel/ignore-blanks-on-a-graph-when-created-by-another-formula/m-p/689867#M32758
<P>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. </P><P>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. </P><P>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)).</P><P> </P><P>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.</P><P> </P><P> </P>Thu, 13 Jun 2019 11:02:49 GMThttps://techcommunity.microsoft.com/t5/excel/ignore-blanks-on-a-graph-when-created-by-another-formula/m-p/689867#M32758thortonisb2019-06-13T11:02:49ZRe: Ignore blanks on a graph, when created by another formula: complicated
https://techcommunity.microsoft.com/t5/excel/ignore-blanks-on-a-graph-when-created-by-another-formula/m-p/690039#M32764
you could try replacing the "" with NA()<BR /><BR />So <BR /><BR />is =IF( VLOOKUP(A2,A3:M79,COLUMN(),0)="" ,NA() ,VLOOKUP(A2,A3:M79,COLUMN(),0) )Thu, 13 Jun 2019 12:26:36 GMThttps://techcommunity.microsoft.com/t5/excel/ignore-blanks-on-a-graph-when-created-by-another-formula/m-p/690039#M32764Wyn Hopkins2019-06-13T12:26:36ZRe: Ignore blanks on a graph, when created by another formula: complicated
https://techcommunity.microsoft.com/t5/excel/ignore-blanks-on-a-graph-when-created-by-another-formula/m-p/691446#M32794
<P><LI-USER uid="9832"></LI-USER> 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!</P>Thu, 13 Jun 2019 23:10:27 GMThttps://techcommunity.microsoft.com/t5/excel/ignore-blanks-on-a-graph-when-created-by-another-formula/m-p/691446#M32794thortonisb2019-06-13T23:10:27Z