View Question
 Question
 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
 ```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 :)`
 gunky-ga rated this answer: `good job guru. its nice to save a lot of time for a modest fee. :)`