Solved Comparing two columns in Excel.

December 12, 2012 at 14:49:00
Specs: Windows Vista
Want to compare two columns and determine one of three things- was there an increase, a decrease or no change with the results able to be "conditionally formatted" to use an icon set for easy reference.

Columns are percentages.


See More: Comparing two columns in Excel.

Report •


✔ Best Answer
December 12, 2012 at 19:42:47
For Conditional Formatting, using an Icon Set
Try this:

With your data like this:
Previous month in cell C3 = 97%
Current month in cell C8 = 98%

Select cell C8
Select Home Tab
Select Conditional Formatting
Scroll down to Icon Set
Select More Rules at the bottom of window

First, at the bottom, in the box next to Icon Style
click and scroll up/down till you find the icon set:
Three Arrows Colored

Next, in the two box's under the word TYPE
on the right side, change them both to NUMBERS

Next, in the two box's under the word VALUE
in the center, change them both to the value: =$C$3

Next, in the small box to left of the VALUE box
for the Green Up Arrow, change the symbol to > (Greater Then)

Next, in the small box to left of the VALUE box
for the Yellow Side Arrow, change the symbol to >= (Greater Then or Equals)

Click OK

You should now have a Green Up Arrow in cell C8

By changing the value in cell C3
you can change which arrow appears in cell C8

MIKE

http://www.skeptic.com/



#1
December 12, 2012 at 15:37:44
Are you comparing these columns cell by cell or a total of all the cells or something else?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 12, 2012 at 17:34:41
cell by cell.

Example:
Previous month personnel strength: 97% (lets call this cell C3)
Current month personnel strength: 98% (and this cell C8)


Report •

#3
December 12, 2012 at 17:45:42
Now I'm really confused. You say that you want to compare 2 columns, yet the examples you gave are both in Column C.

I think we need some more examples of your data and the expected results based on those examples.

Please click on the following line and read the instructions found via that link before posting your example data. Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 12, 2012 at 18:14:33
Prev Month Current Month
STRENGTH: # of Authorized / # of Assigned 94% 95%

Wanting to identify if the strength % went up or down or stayed the same. But I don't see where Excel formulas permits a logical argument of =, >, < together. All results are either "true" of "false". But I need three results: =, <, and >. Then use those results to use the conditional format icon set to easily see if the stength increases, decreased or stayed the same from previous month.


Report •

#5
December 12, 2012 at 19:19:25
It doesn't appear that you honored my request to read the instructions for posting data in this forum which can be found via the link at the bottom of my posts.

Based on that, I'll have to assume that your data looks like this:

                 A                                B                C   
1                                             Prev Month    Current Month
2 STRENGTH: # of Authorized / # of Assigned      94%             95%

I would then use this formula:

=IF(B2>C2,"Decreased",IF(B2<C2,"Increased","Stayed the Same"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 12, 2012 at 19:42:47
✔ Best Answer
For Conditional Formatting, using an Icon Set
Try this:

With your data like this:
Previous month in cell C3 = 97%
Current month in cell C8 = 98%

Select cell C8
Select Home Tab
Select Conditional Formatting
Scroll down to Icon Set
Select More Rules at the bottom of window

First, at the bottom, in the box next to Icon Style
click and scroll up/down till you find the icon set:
Three Arrows Colored

Next, in the two box's under the word TYPE
on the right side, change them both to NUMBERS

Next, in the two box's under the word VALUE
in the center, change them both to the value: =$C$3

Next, in the small box to left of the VALUE box
for the Green Up Arrow, change the symbol to > (Greater Then)

Next, in the small box to left of the VALUE box
for the Yellow Side Arrow, change the symbol to >= (Greater Then or Equals)

Click OK

You should now have a Green Up Arrow in cell C8

By changing the value in cell C3
you can change which arrow appears in cell C8

MIKE

http://www.skeptic.com/


Report •


Ask Question