Excel - comparing data

Microsoft Excel 2007
February 25, 2010 at 05:24:38
Specs: Windows XP
I have two sets of pupil data and I want to compare the two.

example.
john - 125 124
maria 112 93

I want to set up a statement that will alert me if the difference betwen each childs scores have dropped by between 3 and 10 and then another alert if they have dropped by more than 10.
Can anyone help me?


See More: Excel - comparing data

Report •

#1
February 25, 2010 at 08:09:14
Hi,

One way to do this is to use conditional formatting. In this example, the cell with the name will go yellow if the drop is between 3 and 10 and it will go red if the drop is more than 10.

The same process can be used to change the color based on a percentage change rather than an absolute difference.

For this example the names are in column A, initial score in column B and current score in column C

Select all the cells with names in column A.
Assuming that the first name is in cell A2, do this:

From the Ribbon select Home - Styles - Conditional Formatting,
From the drop down select 'Manage Rules' and select 'New Rule'
In the dialog box select the last item in the list 'Use a formula to determine which cells to format'
Enter this formula in the box

=IF(B2-C2>=3,TRUE,FALSE)

Note that TRUE and FALSE do not have double quotes - they are Excel's logical values true and false.
Change the cells referenced (B2 and C2) if the first name is not on row 2 and change columns if the two scores are not in columns B and C.
Click the format button and from the 'Fill' Tab select a yellow color, click OK
Click OK and select 'New Rule'
Select 'Use a formula to determine which cells to format'
Enter this formula in the box
=IF(B2-C2>10,TRUE,FALSE)

Click the format button and from the 'Fill' Tab select a red color, click OK
'Applies to' will show the selected range, and the 'Stop if True' boxes are checked.
Click Apply and OK.

Regards


Report •
Related Solutions


Ask Question