# comparison test

#### dmasvar

##### New Member
Hi I have a question, unfortunately i'm not sure which combination of technique to use:

bAsically, i've got the below

ID Year Amount
123 2001
234 2002
123 2003
234 2004
345 2001
345 2002
456 2001
566 2004
456 2004

What i'm trying to do is test for:
if 2004 collection is 20% greater than 2001 for ID 123, than say "yes"
Get it to test for the entire column of ID to compare between 2004 and 2001

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi

Assuming that the data is in the range A1:C10 and
D1: 123 (the ID being tested)
E1: 2001 (the start year)
F1: 2004 (the end year)
then try
Code:
``=IF(SUMPRODUCT(--(\$A\$2:\$A\$10=\$D\$1),--(\$B\$2:\$B\$10=F1),(\$C\$2:\$C\$10))>SUMPRODUCT(--(\$A\$2:\$A\$10=\$D\$1),--(\$B\$2:\$B\$10=E1),(\$C\$2:\$C\$10))*1.2,"Yes","")``

HTH

Tony

hi

but the problem is ACW, my data for Year is all in 1 column and not two, its is not sorted into 2 columns as in one column for start and one for end of year.

any clues?

Hi

Yes, your source data only has the year in the one column. The entries in D1:F1 are to allow you to select which particular items you want to compare. If you only ever want to select those 3 items, then you could replace the cell reference in the formula with the fixed values.

Tony

ACW,

I appreicate your help, but unfortunatley this formulae is beyond me, could you explain the last part as in replacing ...

"The entries in D1:F1 are to allow you to select which particular items you want to compare. If you only ever want to select those 3 items, then you could replace the cell reference in the formula with the fixed values. "

i've deleted the reference to D1 through to F1 and replace with the values in my column, but i still get no results.

Replies
0
Views
604
Replies
2
Views
114
Replies
0
Views
2K
Replies
7
Views
496
Replies
2
Views
163

1,203,606
Messages
6,056,280
Members
444,854
Latest member
goethe168

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back