comparison test

dmasvar

New Member
Joined
Nov 17, 2004
Messages
33
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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","")

Adjust for ranges as requred.


HTH

Tony
 

dmasvar

New Member
Joined
Nov 17, 2004
Messages
33
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

New Member
Joined
Nov 17, 2004
Messages
33
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,265
Messages
5,577,088
Members
412,764
Latest member
Brawler
Top