Note: when it says "B5" in the explanation below, it actually means "B{current_row}", so for C5 it's B5, for C6 it's B6 and so on. Unless you specify $B$5 - then you refer to one specific cell.
This is supported in Google Sheets as of 2015:
https://support.google.com/drive/answer/78413#formulas
In your case, you will need to set conditional formatting on B5.
- Use the "Custom formula is" option and set it to
=B5>0.8*C5
.
- set the "Range" option to
B5
.
- set the desired color
You can repeat this process to add more colors for the background or text or a color scale.
Even better, make a single rule apply to all rows by using ranges in "Range". Example assuming the first row is a header:
- On B2 conditional formatting, set the "Custom formula is" to
=B2>0.8*C2
.
- set the "Range" option to
B2:B
.
- set the desired color
Will be like the previous example but works on all rows, not just row 5.
Ranges can also be used in the "Custom formula is" so you can color an entire row based on their column values.
There are a few things to consider here:
- Does the list of attributes change significantly over time
- Does the list of attributes require custom user-defined attributes
- Are there different attributes for different schools (i.e. many attributes only apply to one or a few schools)?
If any of these are true, you might think about a properties store approach like EAV, hstore, json fields, xml fields, etc.
If not - if you have a fairly static list of properties where most of them make sense for most of the rows - then there's not really a problem with having them as 60 individual columns. It'll be easier to add indexes for commonly searched for sets of attributes, including partial and composite indexes, etc, and searches - particularly those for many different attributes - will be much faster.
See also: Database design - should I use 30 columns or 1 column with all data in form of JSON/XML?
There's also a compromise option available to you: A main table for the most important details you look up a lot, plus side-tables for logical groupings of attributes. Say:
yearly_summary (
yearly_summary_id serial primary key,
school_id integer,
total_students integer,
...
)
plus
yearly_student_stats(
yearly_summary_id integer primary key references yearly_summary(yearly_summy_id) on delete cascade,
...
)
etc. The integer primary key
that's also a foreign key
means you have an enforced 1:1 (optional) relationship to the other table. This approach can be useful if you have a few logical groupings of attributes that you can cluster into side-tables.
I'd also be surprised if a little more thought didn't reveal things that do make sense to normalize. Do you have year7_blah
, year8_blah
, year9_blah
etc columns? If so: Great candidate for normalization.
Best Answer
I think what you're looking for is 3 conditional formatting rules.
=A2=A1
=A2<A1
=A1<A2
Set the conditional formatting up in cell
A2
and then click on the cell, click on the format painter, and drag it down.