Google Spreadsheet Conditional Formatting for Color Background

Question:

This is probably incredibly simple, actually it has to be, but i need to have a cell show me the variance between two columns.

I'm running two numbers. cell 1 has my starting number, cell 2 has my ending number. I need to know the difference, how much higher or lower cell 2 is than cell 1. So if cell 1 is my starting balance, cell 2 is my ending balance, i need cell 3 to show the difference, and id like to have it change colors from red to green depending on if it has increased or decreased.

I'm not a formula guy, so unless it's super simple i get a little confused. IN excel I accomplish the difference by using ABS (not the color change obviously, but the variance). but I'm not sure how to do this in Google docs. in fact, I've never calculated any formulas in Google before unless it was from a template i downloaded.

the second part might be harder, showing green or red depending on increase or decrease.

I'm doing this online so i can share it with the wife. it's just a budget sheet. i have a detailed budget template, but it confuses her, and i need to make this as simple as possible. 

=================

Me:

Suppose you have one number in Cell A1 and other in Cell A2, then to get the difference between two cells in Cell A3,

Put the following formula in Cell A3:

=A1-A2

And to change the color by comparing A3 to A1 you have to code a script.

===============

Question Continued...

hi and thank you for the answer!

i don't think that will work. correct me if i'm wrong, but isn't that simply going to subtract the second cell from the first?

here's what i'm trying to do in this case.

column a has a few line items, they're dollar values based on income.
column b has the same info, but it's an 'after' column.

So let's say we only have one line item

income 4000 3000

4000 is this weeks income, 3000 is the second column and today's income. it's 1000 less. so that formula would wok

but what if the next week it was

income 3000 5000 

based on that formula wouldn't the result be -2000? when the real answer would be +2000 it's 2000 higher than the previous period.

it's not always subtract, it's the different. so column a plus or minus column b depending on whether the second number is higher or lower than the first.

also, as for writing a script, i assumed i could use conditional formatting? no? i understand the very basics, but not sure how to do that. i can hunt it down and keep trying to figure it out, was just hoping someone knew the correct way to use conditional format.

it's the same instance, so if the second number is lower than the first, it would be red, if it was higher it would be green.

Solution:

Have a look at the following screenshot of my spreadsheet.

As you can see column 1 and column 2 has the some values. And I have the formula:
=arrayformula(if(row(A:A)=1;"Difference";if((A:A="")*(B:B="");"";ABS(A:A-B:B))))
in Cell C1 to get the absolute values from Column1 and Column2.

And now to get the color conditionally, that is if Column A's value is greater than Column B's value then Column C's color should be "Green".

And if Column B's value is greater than Column A's value then the color should be "Red". To get such conditional formatting you have to insert following script in script editor in your Spreadsheet.

function onEdit(e)
{
  myfunction();
}

function myfunction() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var datarange = sheet.getDataRange();
  var numRows = datarange.getNumRows();
  var values = datarange.getValues();

  var dataformat = datarange.getBackgrounds();
  
  for (var i = 0; i <= numRows - 1; i++)
  {
    if( values[i][0] - values[i][1] < 0 )
      dataformat[i][2] = "RED";
    else if( values[i][0] - values[i][1] > 0 )
      dataformat[i][2] = "GREEN";
    else
      dataformat[i][2] = "WHITE";
  }

  datarange.setBackgrounds(dataformat);

}

So, now whenever you edit your spreadsheet, the above script will auto calculate and change the color of Column C accordingly.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive