how do we remove both #N/A error and 0 in Vlookup, thank you.

[pedie]

I have a table from where i do a vlookup.
when i try to remove by 0 from appearing when there is no value in table
=IF(ISBLANK(VLOOKUP($C$3,CompletedTrainingsonly!$A$20:$V$35,2)),"",(VLOOKUP($C$3,CompletedTrainingsonly!$A$20:$V$35,2)))

#n/A appears
and when i use
=IF(ISNA(VLOOKUP($C$3,CompletedTrainingsonly!$A$20:$V$35,2)),"",(VLOOKUP($C$3,CompletedTrainingsonly!$A$20:$V$35,2)))

when the cell is blank 0 appears...

Is there a formula to avoid getting any of this error/ result and get blank with there is no value in table

Thanks again!

 

[tusharm]

How are you "removing the grade?"

Upon checking my spreadsheet I see that I have a problem in the Quiz section. Let me explain:

If I enter three Quiz grades in (as seen) AE374= 7; AF374= 8 ; AG374= 6 and the count blank in AJ374 = 3 and the division takes place according to the formula that is set in AI374 but when I remove a grade from AH374 (as seen) then the count blank remains on 3 which was the previous number of Quiz grades and it divides the 2 numbers by 3 and there are only two blanks. Is there a solution for this?
2 Per

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 21px;"><col style="width: 18px;"><col style="width: 20px;"><col style="width: 23px;"><col style="width: 40px;"><col style="width: 27px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>AE</td><td>AF</td><td>AG</td><td>AH</td><td>AI</td><td>AJ</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">371</td><td colspan="5" style="font-family: Maiandra GD; font-size: 14pt; text-align: center;">Quiz</td><td style="font-family: Maiandra GD; font-size: 14pt;">
</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">372</td><td colspan="5" style="font-family: Maiandra GD; font-size: 12pt; text-align: center;">10%</td><td style="font-family: Maiandra GD; font-size: 12pt;">
</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">373</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">1</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">2</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">3</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">4</td><td style="color: rgb(0, 0, 255); font-weight: bold; font-family: Maiandra GD; font-size: 12pt; text-align: center;">T</td><td style="color: rgb(0, 0, 255); font-weight: bold; font-family: Maiandra GD; font-size: 12pt; text-align: center;">#</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">374</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">7</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">8</td><td style="font-family: Maiandra GD; font-size: 12pt;">
</td><td style="font-family: Maiandra GD; font-size: 12pt;">
</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: center;">5</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: center;">3</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">375</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">8</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">8</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">5</td><td style="font-family: Maiandra GD; font-size: 12pt;">
</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: center;">7</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: center;">3</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>AI374</td><td>=SUM(AE374:AH374)/AJ374</td></tr><tr><td>AJ374</td><td>=4-COUNTBLANK(AE374:AH374)</td></tr><tr><td>AI375</td><td>=SUM(AE375:AH375)/AJ375</td></tr><tr><td>AJ375</td><td>=4-COUNTBLANK(AE375:AH375)</td></tr></tbody></table></td></tr></tbody></table>

 

[kjacw]

When I say 'remove the grade', I am referring to when I was checking to see if the spreadsheet was working correctly by deleting a quiz grade from AG374 and leaving it blank. This is when I realized that when I removed that grade the count blank column (AJ374) continued to say "3" instead of changing for "3" to "2".

When the school ends and I change the name of the students how will I solve this problem of removing all grades in preparation to input all new information for new students?

 

[tusharm]

Yes, but *how* are you removing the grade? What's the keystroke / mouse acts involved? For example, are you selecting the cell and pressing the space bar?

When I say 'remove the grade', I am referring to when I was checking to see if the spreadsheet was working correctly by deleting a quiz grade from AG374 and leaving it blank. This is when I realized that when I removed that grade the count blank column (AJ374) continued to say "3" instead of changing for "3" to "2".

When the school ends and I change the name of the students how will I solve this problem of removing all grades in preparation to input all new information for new students?

 

[kjacw]

Yes that is correct I am selecting the cell and pressing the space bar to remove the contents of that cell.

 

[tusharm]

That is *not* deleting the content of the cell. It is replacing a number with a space character.

To actually delete the cell content, select the cell and press the 'Delete' key.

Yes that is correct I am selecting the cell and pressing the space bar to remove the contents of that cell.