hello,
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.

thanks

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
((b-a)/a)*100.

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.

Cheers,
answerguru-ga

---

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.

answerguru-ga

---

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:

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

dare it be.

---

Clarification of Answer by answerguru-ga on 23 Nov 2005 07:41 PST

Good to know - thanks for filling us in :)

good job guru. its nice to save a lot of time for a modest fee. :)