mercredi 29 avril 2020

Return empty cell instead of 0 in Google Sheets when data being displayed is an array from another sheet

I have a Google Sheet workbook with multiple sheets being used to track COVID-19 cases in institutions across the country. The built-in Google Sheets geo chart works perfectly for the data visualization I need to accomplish, with one issue: It currently can't differentiate between actual 0 and "no data", which super skews how the

chart displays colors

(essentially you can choose what color to use on the map for high value, mid value, low value, and no value. Where it should be using the color for "no value", it uses the low value color instead which makes the visualization confusing.)

The reason it's doing that is the array it's using as its data source contains zeroes to represent "no data available".

The array is imported from a different sheet by using ={'State Totals'!N4:P54}. I found an explanation for how to generally use a formula to return empty cells, the example there being =if(B2-C2>0, B2-C2, " ").

I'm extremely noob when it comes to these formulas, and I cannot figure out if I can nest an IF condition in an array import, or vice versa, or... what or how.

Here's a link to the sheet in question, if that helps at all. Really I just need a formula that

  • Imports the array values
  • Returns empty cells in place of zeroes where they appear

I don't want to affect the origin sheet's zero handling, just the one that the chart's using. (I also am absolutely not being paid enough to try and rig up a better map with Google Data Queries instead of the in-built Google Sheets chart maker, so here's to hoping it's a simple matter of syntax.)

Aucun commentaire:

Enregistrer un commentaire