Question on usage of MAX / COLLECT in a formula

Options
edschre
edschre ✭✭
edited 05/11/23 in Formulas and Functions

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!

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!