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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,624
Messages
5,832,742
Members
430,160
Latest member
a_majda

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top