tally the difference between 2 columns

[cpliu]

This is for surveys comparing before and after.
For example, there are 2 columns below, 1st is before, 2nd is after
3,5
5,5
4,5
4,5
2,5
3,5
....

Is there a way I can calculate:
total number of difference beween 2 columns for increase of 1, 2, 3,
and 4 or decrease
total number of each type of increase: ie. total 5 to 5, 4 to 5, total
of 3 to 5, etc

I've explored COUNTIFbut how do you have condistions in the criteria
(column b - column a = 1) or mulitple conditions (column b - column a
= 1 and column a = 4)?

thanks for your help,

 

[Jim Cone]

Re: " total number of difference"...
=SUMPRODUCT(--(B5:B10<>C5:C10))
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"cpliu"
wrote in message
This is for surveys comparing before and after.
For example, there are 2 columns below, 1st is before, 2nd is after
3,5
5,5
4,5
4,5
2,5
3,5
....
Is there a way I can calculate:
total number of difference beween 2 columns for increase of 1, 2, 3,
and 4 or decrease
total number of each type of increase: ie. total 5 to 5, 4 to 5, total
of 3 to 5, etc
I've explored COUNTIFbut how do you have condistions in the criteria
(column b - column a = 1) or mulitple conditions (column b - column a
= 1 and column a = 4)?
thanks for your help,

 

[cpliu]

Re: " total number of difference"...
=SUMPRODUCT(--(B5:B10<>C5:C10))

Thank you for the help. This works for finding the total rows that
have different values but it can't do the other tasks I need.

For example: with 2 columns below,
4,5
4,5
2,5
3,5
4,5
5,5
5,5
4,5
4,5
3,5
4,4
2,4

I'd like to find:

5 to 5 = 2 (total with no change before and after)
4 to 5 = 5 (total of changes from 4 to 5)
4 to 4 = 1
3 to 5 = 2
2 to 5 = 1
2 to 4 = 1

increase of 1 = 5
increase of 2 = 3
increase of 3 = 1
no change = 3

Thanks,

 

[Harald Staff]

I'd use two helper columns for this, and create a pivot table from their
values.

D column
=B1&C1
E column
=C1-B1

Best wishes Harald

Re: " total number of difference"...
=SUMPRODUCT(--(B5:B10<>C5:C10))

Thank you for the help. This works for finding the total rows that
have different values but it can't do the other tasks I need.

For example: with 2 columns below,
4,5
4,5
2,5
3,5
4,5
5,5
5,5
4,5
4,5
3,5
4,4
2,4

I'd like to find:

5 to 5 = 2 (total with no change before and after)
4 to 5 = 5 (total of changes from 4 to 5)
4 to 4 = 1
3 to 5 = 2
2 to 5 = 1
2 to 4 = 1

increase of 1 = 5
increase of 2 = 3
increase of 3 = 1
no change = 3

Thanks,