u/cowsruleusall

Hi folks,

I have a set of data across multiple rows/columns, for which I'm trying to use conditional formatting to hide text if it meets various criteria. I have no problem with simple criteria like "value must exceed 0.01". However, one set of criteria is that the value for all the data in a column, must exceed a reference value at the very bottom of the column.

Sample image here for a smaller dataset.

For example, in the sample image, all of the values B2:B7 which are less than or equal to B9 should be hidden, all the values C2:C7 which are less than or equal to C9 should be hidden, etc.

I'm aware that I could do this on a column-by-column basis with if-then statements. But is there an efficient way for me to use a formula in Conditional Formatting that will allow me to do this efficiently across the entire dataset?

Referring to the sample image, I would select all data B2:L7, select Conditional Formatting, and then New Rule. From here, I've tried using "Format only cells that contain", using the criterion "Less than or equal to", and then I tried entering B9:L5, but it returns an error saying that the reference can only be a single cell.

I can't seem to figure out how to use the "Use a formula to determine which cells to format" to do this and would greatly appreciate help.

IT'S (ALMOST) TIME!!! My favourite event of the year is the SPG Subreddit Birthday, in part becuase it overlaps almost perfectly with my own birthday. So of course, I'm kicking things off.

In years past, we've asked subreddit members what they'd like to see during subreddit events, but it's always been informal. This time around? We want to make sure that we really know what SPG'ers are looking for. So we've developed a series of survey questions to ask exactly that!

On top of that - I was in a few big industry discussions recently. Meetings about changing demographics and who's buying what, changing market dynamics for synthetics and well-cut gems, etc. And hot damn, the industry holds majorly outdated assumptions about who's buying gems and what kinds of things people want.

Since Michelle and I are hardcore data nerds with science backgrounds, I figured we can kill two birds with one stone - get survey data to help understand what SPG'ers want, and get enough data to disprove some industry assumptions 😃

 

THE GIVEAWAY

This is a free gemstone giveaway! Event ends Sunday, June 7th, at 23:59:59 Toronto time.

Want a free gem? Fill out our Short Form survey. Your name gets entered in a random drawing for a free gem.

Once the giveaway event is over, we'll randomly select the winner(s). For every 25 people who fill the survey out, I'll draw an additional name. So tell your buddies in SPG to fill out the survey - it won't lower your chances!

Want an even better gem??? Fill out both the Short Form and the Extended survey, and you'll get entered to win an even more valuable gem.

We understand that the surveys are a bit long, anywhere from 6-10 minutes, so to sweeten the pot, the winner(s) will get to pick their choice from a selection of gems.

WHO CAN PARTICIPATE?

Anyone! Anyone worldwide, except people in countries that Canada currently has a formal trade embargo on.

THE GEMS

• Pink - two 5mm round holmium-YAG
• Red - two 4mm round "anthill" pyrope garnets from AZ
• Orange - one 5mm trillion tourmaline from Tanzania
• Yellow - one 9x7mm oval Verneuil #20 sapphire
• Lime - one 6x4mm pear peridot from AZ
• Green - one 8x6mm pillow Verneuil #85 sapphire
• Cyan - one 7mm trillion Verneuil #138 spinel
• Blue - two 4mm round iolite from Odisha, India
• Purple - one 6x4mm oval amethyst from Zambia

Short Form rewards - red, yellow, lime, purple

Extended rewards - pink, orange, green, cyan, blue...PLUS be entered in the Short Form drawing too!

Edit: welp looks like we've had a ton of people respond so I'll have to pick out some extra rewards.

 

THE SURVEYS

Short Form Survey

The Short Form survey asks some general questions about your previous participation in SPG events, and what your thoughts are. Did you like the structure? What did you think of the games and giveaways? Do we have enough variety, in gems and in price points?

Extended Survey

Want something a bit nicer? In that case, fill out the Short Form survey, AND the Extended survey. The Extended Survey asks more detailed questions about specific gem types, demographics, and various other things to help guide the future of SPG. This is the place to get really specific.