Google Answers Logo
View Question
Q: simple excel equation ( Answered 5 out of 5 stars,   0 Comments )
Subject: simple excel equation
Category: Computers > Programming
Asked by: gunky-ga
List Price: $7.50
Posted: 22 Nov 2005 13:57 PST
Expires: 22 Dec 2005 13:57 PST
Question ID: 596416
i would like an excel equation (but i have quattro pro) that would
accomplish the following:

say there are 2 columns of numbers, i would like to compute the %
difference between the 2 columns for each row. this is easy enough,
but i need it to be able to give a zero value if the difference
results in an error as a result of non-numeric data or an
indeterminate. it would be nice if the resulting column's rows would
be color coded for positive and negative % values (say green for pos.
and red for neg.).
i know when trying to explain these types of scenarios things can be
as clear as mud, so let me know if this is the case.

Subject: Re: simple excel equation
Answered By: answerguru-ga on 22 Nov 2005 18:37 PST
Rated:5 out of 5 stars
Hi gunky-ga,

Given that you have pairs of values in column A and B, the result you
are seeking can be achieved by using a conditional IF statement in
your function. We test whether either number is non-numeric, and we
only provide a calculated value if the calculation is possible. To
calculate the percentage difference I am using the standard formula of

So, when calculating the percentage difference between A1 and B1, for
example, the function is:

=IF(AND(ISNUMBER(A1),ISNUMBER(B1)), ((B1-A1)/A1)*100, 0)

We can colour code these result using what is known as conditional
formatting in Excel. Here are the steps:

1. Select the cells for which a value is being calculated (I just
chose the entire column C)
2. Go to Format > Conditional Formatting
3. Place the first condition as "Cell value is greater than 0"
4. Set the Format for this condition by clicking the Format button and
changing the colour as desired.
5. Click "Add" to create a second condition, which is "Cell value is
less than or equal to 0".
6. Set the Format for the second condition
7. Click OK

You should now have cells that are formatted on the basis of the value
that is being calculated. You're done!

If you have any problems understanding the information above, please
let me know by posting a clarification.

Thanks for using Google Answers.


Request for Answer Clarification by gunky-ga on 22 Nov 2005 18:56 PST
guru, ur a good kid. this looks great, but when i put equation into
spreadsheet, i get invalid issues?? i am using quattro pro in excel
mode fyi. if this is the problem, then i will grin and bear it.

thanks for your time.

Clarification of Answer by answerguru-ga on 22 Nov 2005 19:08 PST
I'm not certain if it is a problem - I unfortunately don't have
Quattro Pro to test this against. From the little I know about this
"Excel mode", it is a compatibility add-on that may not contain all of
the functionality of Excel.


Request for Answer Clarification by gunky-ga on 23 Nov 2005 07:16 PST
just for completeness, quattro pro's version of the equation would be:


dare it be.

Clarification of Answer by answerguru-ga on 23 Nov 2005 07:41 PST
Good to know - thanks for filling us in :)
gunky-ga rated this answer:5 out of 5 stars
good job guru. its nice to save a lot of time for a modest fee. :)

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy