Show blank if blank, show 0% if 0% to Average

[SMAsh67]

I'm making a score sheet that I have to host on SharePoint so I can't use anything that isn't a straight-up formula. The problem I can't figure out is how to do the averages on the last page. I need it to be a running total, and not count the blanks as 0% when averaging.

It's a sort of scorecard for each week in Feb (a tab for each week) and the last tab does an average as the participants are scored.

I have this, which works but shows 0% if the cell is blank so it doesn't work for what I want:

=VLOOKUP($B:$B,'February 2020 Week 1'!$C:$S,5,FALSE)

I tried this:

=IF(VLOOKUP($B:$B,'February 2020 Week 1'!$C:$S,5,FALSE)="","",VLOOKUP($B:$B,'February 2020 Week 1'!$C:$S,5,FALSE))

But it just makes everything blank.

Any help would be greatly appreciated!

 

[JustynaMK]

This should do the trick:

=VLOOKUP($B:$B,'February 2020 Week 1'!$C:$S,5,FALSE)&""

 

[SMAsh67]

Thank you, but that also makes it show the cell as blank even if it isn't.

I think I'm just going to set up some conditional formatting as a workaround

 

[JustynaMK]

Hmm weird, could you please paste an example of your dataset?

Here's my view for "a":

And the same formula for when cell G1 contains no value:

 

[SMAsh67]

I have a "fiddle with" copy here if you want to take a look... http://createdbysmash.com/ScoreSheet.xlsx

 

[JustynaMK]

Thanks for sharing the file!
Here's what I have on "February 2020 Week 1" sheet:

When updating the formula in "QA Final", cell C12, to =VLOOKUP($B:$B,'February 2020 Week 1'!$C:$S,2,FALSE)&"" it shows:

Fair enough, &"" syntax at the end of the formula is turning numeric values into text. To mitigate that, you can apply the following formula:

=IF(LEN(VLOOKUP($B:$B,'February 2020 Week 1'!$C:$S,2,FALSE)&""),VLOOKUP($B:$B,'February 2020 Week 1'!$C:$S,2,FALSE),"")