comparison test

[dmasvar]

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

 

[acw]

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

=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","")

Adjust for ranges as requred.


HTH

Tony

 

[dmasvar]

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?

 

[acw]

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

 

[dmasvar]

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.