Question on usage of MAX / COLLECT in a formula

edschre

I'm trying to create a column to have the maximum 'Amount' for an 'Opportunity' in a given 'week':

I have three columns:

Week, Opportunity, Amount

1,ACME,100

1,ACME,200

1, NEWCO, 100

1,NEWCO,50

2,ACME,200

2,ACME,300

2, NEWCO, 40

2,NEWCO,30

And trying to create a column to capture the MAX for that Opportunity in a given week:

Week, Opportunity, Amount, MAX

1,ACME,100,200

1,ACME,200,200

1, NEWCO, 100,100

1,NEWCO,50,100

2,ACME,200,300

2,ACME,300,300

2, NEWCO, 40,40

2,NEWCO,30,40

I 'think' I'm close, but can't get over the hump!

Maybe I should be using VLOOKUP? but this is what I was thinking for MAX/COLLECT:

=MAX(COLLECT(Amount:Amount, Opportunity:Opportunity, Week:Week = [@Week]))

Thanks in advance!

Leibel S

@edschre

Try this:

=MAX(COLLECT(Amount:Amount, Opportunity:Opportunity, @cell=Opportunity@row, Week:Week,@cell=Week@row))

Leibel S

@edschre

Try this:

=MAX(COLLECT(Amount:Amount, Opportunity:Opportunity, @cell=Opportunity@row, Week:Week,@cell=Week@row))

edschre

Perfect!!